Skip to Content
0

SQL question, use LEFT OUTER JOIN or another way?

Jul 12, 2017 at 03:36 PM

32

avatar image

I have two tables, both have the same fields, calendar week and revenue.

Table 2015 has all 52 weeks with revenue, table 2016 only has 11 rows.

If I do a LEFT OUTER JOIN (SUM ((2015 + 2016) /2)), I will get only “?” as revenue for 2017 after 12th row.
How can I fill 2017 with numbers for all 52 weeks?

Reply
tables.jpg (40.4 kB)
tables.jpg (38.8 kB)
SQL
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Ram Yaganti Jul 12, 2017 at 05:47 PM
0

use Union.

CREATE COLUMN TABLE T2015 (WEEK INT, REVENUE INT);
CREATE COLUMN TABLE T2016 (WEEK INT, REVENUE INT);


INSERT INTO "T2015" VALUES (1, 1);
INSERT INTO "T2015" VALUES (2, 2);
INSERT INTO "T2015" VALUES (3, 3);
INSERT INTO "T2015" VALUES (4, 4);


INSERT INTO "T2016" VALUES (1, 1);
INSERT INTO "T2016" VALUES (2, 2);




SELECT WEEK, AVG(REVENUE) FROM 
(
SELECT * FROM T2015
UNION ALL
SELECT * FROM T2016
) 
GROUP BY WEEK
Show 1 Share
10 |10000 characters needed characters left characters exceeded

Perfect, works like a charm.

0