Create view from two tables

Databases
1

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

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!

3

please repeat the exact columns you want in the result set

4

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?

5 
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

:slight_smile:

6

create a view for the UNION, then create a view for the outer query

7

The summary needs to be on what level? Please give a data example.