Skip to Content
avatar image
Former Member

SQL question, use LEFT OUTER JOIN or another way?

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jul 12, 2017 at 05:47 PM

    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
    
    Add comment
    10|10000 characters needed characters exceeded