CODEWITHSUNDEEP
sqljoinunion
user8162554
user8162554

Reputation:

Basic SQL - Adding Year on Year Revenue Numbers

So I'm a bit newer to SQL and am having trouble with joining multiple queries in a singular table.

I have no one to talk to about this at my company and need some basic advice on how to create tables with year on year numbers.

My basic scripts goes as follows and I'm not sure how to link the 2 so that I can show the revenue for 2 years side by side.

SELECT a.date_key, b.account_name,a.client_id,SUM(a.revenue) as "Revenue 2019" FROM spreadsheet as a
LEFT JOIN account_file as b on a.client_id = b.client_id
WHERE date_key >= 2019 and date_key < 2020. 
GROUP BY a.date_key, a.client_id, b.account_name


SELECT a.date_key, b.account_name,a.client_id,SUM(a.revenue) as "Revenue 2018" FROM spreadsheet as a
LEFT JOIN account_file as b on a.client_id = b.client_id
WHERE date_key >= 2018 and date_key < 2019. 
GROUP BY a.date_key, a.client_id, b.account_name

I have seen examples of how this is supposed to work but can't quite wrap my head around it and could use a hand.

Upvotes: 0

Views: 41

Answers (2)

Parfait
Parfait

Reputation: 107652

Consider conditional aggregation where you move your WHERE conditions to CASE statements for multiple columns. Also please heed Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3)

SELECT a.account_name
       , s.client_id
       , SUM(CASE 
                  WHEN s.date_key >= 2019 AND s.date_key < 2020 
                  THEN s.revenue 
             END) AS "Revenue 2019"
       , SUM(CASE
                  WHEN s.date_key >= 2018 AND s.date_key < 2019 
                  THEN s.revenue 
             END) AS "Revenue 2018"
       ... 

FROM spreadsheet AS s
LEFT JOIN account_file AS a 
    ON s.client_id = a.client_id
GROUP BY a.account_name
         , s.client_id

Upvotes: 0

Popeye
Popeye

Reputation: 35910

You need to use conditional aggregation but do not use datekey in the GROUP BY column as follows:

SELECT a.account_name, s.client_id,
       SUM(CASE WHEN s.date_key >= 2019 AND s.date_key < 2020 
                THEN s.revenue 
            END) AS "Revenue 2019",
       SUM(CASE WHEN s.date_key >= 2018 AND s.date_key < 2019 
                THEN s.revenue 
           END) AS "Revenue 2018"
FROM spreadsheet AS s
LEFT JOIN account_file AS a 
    ON s.client_id = a.client_id
WHERE s.date_key >= 2018 AND s.date_key < 2020
GROUP BY a.account_name, s.client_id

Upvotes: 1

Related Questions