Calculating Percentage Difference

Question

I am looking to perform a query that will calculate the product revenue percentage difference from 2019 to 2020 for each state. Can anyone lead me in the right direction? I attached a screenshot of my output for the code I am using.

Screen of Output Here

SELECT SUM(PRODUCT_REVENUE) AS Product_Revenue, Region, Year
FROM Table1
WHERE NOT Region = 'Not Set'
GROUP BY Region, Year
ORDER BY Region ASC

Answer 1

Assuming that you want to select NULL when one or both revenues of 2019 or 2020 are zero, you can do something like this:

SELECT x.region, x.revenue2019, x.revenue2020,
CASE WHEN COALESCE(x.revenue2019,0) = 0 OR COALESCE(x.revenue2020,0) = 0 THEN NULL
ELSE ROUND(x.revenue2020 * 100 / x.revenue2019,2) END AS percentage 
FROM 
(SELECT
region,
SUM(CASE WHEN year = 2019 THEN product_revenue ELSE 0 END) AS revenue2019,
SUM(CASE WHEN year = 2020 THEN product_revenue ELSE 0 END) AS revenue2020
FROM table1
WHERE region != 'Not Set'
GROUP BY region) x
ORDER BY x.region

I created an example here to replicate it: db<>fiddle

There are further possibilities, you could also do this without a subquery, but I think this wouldn't be good to read. You should take care to prevent dividing by zero exceptions (that's why I select NULL in this case).