I have a table like below (tablename: sales)
| sales_datetime | sales | salesman |
|---|---|---|
| 2022-08-01 09:00:00 | 100 | John |
| 2022-08-01 11:00:00 | 200 | John |
| 2022-08-02 10:00:00 | 100 | Peter |
| 2022-08-02 13:00:00 | 300 | John |
| 2022-08-04 14:00:00 | 300 | Peter |
| 2022-08-05 12:00:00 | 100 | John |
| 2022-08-05 16:00:00 | 200 | John |
From that table I want to make a summary sales for 5 days period for each salesman. So the summary table that I want is look like this
| periode | total_sales | salesman |
|---|---|---|
| 2022-08-01 | 300 | John |
| 2022-08-01 | 0 | Peter |
| 2022-08-02 | 300 | John |
| 2022-08-02 | 100 | Peter |
| 2022-08-03 | 0 | John |
| 2022-08-03 | 0 | Peter |
| 2022-08-04 | 0 | John |
| 2022-08-04 | 300 | Peter |
| 2022-08-05 | 300 | John |
| 2022-08-05 | 0 | Peter |
I have created following query (PSQL) but the results were not same as I want. Assume today is 2022-08-05
with dateseries as
(select generate_series(current_date-'4 days'::interval,
current_date::date,
'1 day'::interval)::date as periode)
select d.periode,coalesce(sum(s.sales),0) as total_sales,s.salesman from dateseries d
left outer join sales s
on d.periode=s.sales_datetime::date
group by d.periode, s.salesman order by d.periode
results:
| periode | total_sales | salesman |
|---|---|---|
| 2022-08-01 | 300 | John |
| 2022-08-02 | 300 | John |
| 2022-08-02 | 100 | Peter |
| 2022-08-03 | 0 | (NULL) |
| 2022-08-04 | 300 | Peter |
| 2022-08-05 | 300 | John |
Any advices would be so great. Thank you
Step by step first aggregate the daily sales per salesperson (aggregated_sales CTE), create a list of days to report (days CTE), create a list of salesmen (salesmen CTE) and then query the sales for each day/salesman pair.
with aggregated_sales as
(
select sales_datetime::date sales_date, sum(sales) sales, salesman
from sales group by sales_datetime::date, salesman
),
days(sales_date) as
(
select d::date
from generate_series('2022-08-01', '2022-08-08', interval '1 day') d
),
salesmen (salesman) as
(
select distinct salesman from sales
)
select sales_date, coalesce(sales, 0) sales, salesman
from (select * from days cross join salesmen) fl
left outer join aggregated_sales ags using (sales_date, salesman);
The query may be shorter if CTEs are inlined yet I think that clarity and readability are more important than mere size.
In order to "make a summary sales for 5 days period for each salesman" replace generate_series('2022-08-01', '2022-08-08', interval '1 day') with generate_series(current_date - 4, current_date, interval '1 day').