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

Desired Output would look a little something like this:

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)

CodePudding user response：

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

CodePudding user response：

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;