Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2007
    Posts
    9

    Unanswered: Create view from two tables

    Hi,
    Guys how to create view from two tables to display daily, monthly and yearly summary. I want to create view with following columns but can't work out the join query. Branchcode is the common in two tables. Please help.

    select id, branchcode, monthyear, sum(total) as Received, sum(totalpaid) as Paid, sum(total-totalpaid) as Balance from deposit,expense where branchcode = 'x' and creationdate >= 'commondate' and invoicedate >= 'commondate' group by monthyear;

    table deposit:
    id
    branchcode
    creationdate
    monthyear
    total

    select id, branchcode, creationdate, sum(total) from deposit where branchcode = 'x' and creationdate >= 'somedate' group by monthyear;

    table expense:
    id
    branchcode
    invoicedate
    monthyear
    totalpaid

    select id, branchcode, invoicedate, sum(totalpaid) from expense where branchcode = 'x' and creationdate >= 'somedate' group by monthyear;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by slamdunk View Post
    Hi I want to create view with following columns but can't work out the join query.
    please repeat the exact columns that you wish to see in the result set
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    9
    r937,

    I need branchcode, monthyear, sum(deposit.total) as Received, sum(expense.totalpaid) as Paid, sum(deposit.total-expense.totalpaid) as Banance group by monthyear


    select branchcode, monthyear, sum(total) as Received, sum(totalpaid) as Paid, sum(total-totalpaid) as Balance from deposit,expense where branchcode = 'x' and creationdate >= 'commondate' and invoicedate >= 'commondate' group by monthyear;

    Thanks!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT branchcode
         , monthyear
         , SUM(Received) as Sum_Received
         , SUM(Paid) as Sum_Paid
         , SUM(Received - Paid) as Banance 
      FROM ( SELECT branchcode
                  , monthyear
                  , SUM(total) AS Received
                  , 0.00       AS Paid
               FROM deposit
              WHERE creationdate >= 'commondate'
             GROUP
                 BY branchcode
                  , monthyear
             UNION all
             SELECT branchcode
                  , monthyear
                  , 0.00       AS Received
                  , SUM(totalpaid) AS Paid
               FROM expense
              WHERE invoicedate >= 'commondate'
             GROUP
                 BY branchcode
                  , monthyear
           ) AS d 
    GROUP
        BY branchcode
         , monthyear
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Posts
    9
    Quote Originally Posted by r937 View Post
    Code:
    SELECT branchcode
         , monthyear
         , SUM(Received) as Sum_Received
         , SUM(Paid) as Sum_Paid
         , SUM(Received - Paid) as Banance 
      FROM ( SELECT branchcode
                  , monthyear
                  , SUM(total) AS Received
                  , 0.00       AS Paid
               FROM deposit
              WHERE creationdate >= 'commondate'
             GROUP
                 BY branchcode
                  , monthyear
             UNION all
             SELECT branchcode
                  , monthyear
                  , 0.00       AS Received
                  , SUM(totalpaid) AS Paid
               FROM expense
              WHERE invoicedate >= 'commondate'
             GROUP
                 BY branchcode
                  , monthyear
           ) AS d 
    GROUP
        BY branchcode
         , monthyear
    R937,

    Thanks for the code. I tried to create a view with this query but it returns an error "View's SELECT contains a subquery in the FROM clause". Any workaround?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by slamdunk View Post
    Any workaround?
    create a view for the UNION, then create a view for the outer query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •