Create View with Two columns in Two Tables

Question

There are two tables: sales and expenses. sales has two columns: sale_date and price, expenses table has two columns: date and amount. also have a view with days in year. (Create View with 365 days)

i want to add another two columns to this view: sales and expenses with data in sales and expenses tables.

example :

Sales Table

sale_date   price

05-DEC-11   1,500
05-DEC-11     500
11-DEC-11     800
17-DEC-11   2,400

Expenses Table

date        amount

06-DEC-11      300
11-DEC-11      100
11-DEC-11      400
15-DEC-11    1,100

View should create as below

date        sales   expenses

01-JAN-11    null    null
02-JAN-11    null    null
03-JAN-11    null    null
04-JAN-11    null    null
05-JAN-11    null    null

...


05-DEC-11   2,000    null
06-DEC-11    null     300
07-DEC-11    null    null
08-DEC-11    null    null
09-DEC-11    null    null
10-DEC-11    null    null
11-DEC-11     800     500
12-DEC-11    null    null
13-DEC-11    null    null
14-DEC-11    null    null
15-DEC-11    null   1,100
16-DEC-11    null    null
17-DEC-11   2,400    null

...


28-DEC-11   null     null
29-DEC-11   null     null
30-DEC-11   null     null
31-DEC-11   null     null

sales and expenses columns in view created with sum of price and amount Columns in sale and expenses table group by date in those two tables.

how could i do this ?

I'm not sure but did you try to create that view on your own before you posted here? — Lion, Dec 18 '11 at 02:58
@Lion i tried to do that. but unable to fill view with sum of columns group by date — Bishan, Dec 18 '11 at 03:17

score 0 · Accepted Answer · answered Dec 18 '11 at 05:22

This should get you there:

SELECT the_day, price, amount
  FROM year_days LEFT JOIN (SELECT sale_date, SUM(price) price
                              FROM sales
                             GROUP BY sale_date) ON the_day = sale_date
                 LEFT JOIN (SELECT expense_date, SUM(expenses) amount
                              FROM expenses
                             GROUP BY expense_date) ON the_day = expense_date
 ORDER BY the_day;

THE_DAY        PRICE     AMOUNT
--------- ---------- ----------
...
01-DEC-11
02-DEC-11
03-DEC-11
04-DEC-11
05-DEC-11       2000
06-DEC-11                   300
07-DEC-11
08-DEC-11
09-DEC-11
10-DEC-11
11-DEC-11        800        500
12-DEC-11
13-DEC-11
14-DEC-11
15-DEC-11                  1100
16-DEC-11
17-DEC-11       2400
18-DEC-11
...

How can i write above query with query in [http://stackoverflow.com/questions/8531269/create-view-with-365-days] as a one query ? — Bishan, Dec 19 '11 at 06:52

Create View with Two columns in Two Tables

1 Answers1