SQL: How to return revenue for specific year

Question

I would like to show the revenue for a specific year for all customers regardless of whether or not they have revenue data for the specific year. (in cases they dont have data for the specific year, a filler like 'no data' would work)

Sample Data looks like:

Table 1

Customer	Price	Quantity	Order Date
xxx	12	5	1990/03/25
yyy	15	7	1991/05/35
xxx	34	2	1990/08/21

Desired Output would look a little something like this:

Customer	Revenue (for 1990)
xxx	128
yyy	no data

Getting the total revenue for each would be:
SELECT Customer,

SUM(quantity*price) AS Revenue

but how would i go about listing it out for a specific year for all customers? (incl. customers that dont have data for that specific year)

Do a GROUP BY. Use a case expression to do conditional aggregation. — jarlh, Mar 25 '22 at 13:50

score 0 · Accepted Answer · answered Mar 25 '22 at 14:11

We can use a CTE or a sub-query to create a list of all customers and another to get all years and the cross join them and left join onto revenue. This gives an row for each customer for each year. If you add where y= you will only get the year requested.

CREATE TABLE revenue(
Customer varchar(10),
Price int,
Quantity int,
OrderDate date);

insert into revenue values
('xxx',   12,5,'2021-03-25'),
('yyy',   15,7,'2021-05-15'),
('xxx',   34,2,'2022-08-21');

with cust as 
(select distinct customer c from revenue),
years as
(select distinct year(OrderDate) y from revenue)
select 
y "year",
c customer ,
sum(price*quantity) revenue
from years 
cross join cust 
left join revenue r
on cust.c = r.customer and years.y = year(OrderDate)
group by 
c,y,
year(OrderDate)
order by y,c

year | customer | revenue
---: | :------- | ------:
2021 | xxx      |      60
2021 | yyy      |     105
2022 | xxx      |      68
2022 | yyy      |    null

db<>fiddle here

score 0 · Answer 2 · answered Mar 25 '22 at 14:21

You would simply use group by and do the sum in a subquery and left join it to your customers table. ie:

select customers.Name, totals.Revenue
from Customers
Left join 
( select customerId, sum(quantity*price) as revenue
  from myTable
  where year(orderDate) = 1990
  group by customer) totals on customers.CustomerId = myTable.customerId;

SQL: How to return revenue for specific year

2 Answers2