Query for displaying count per type for each date in range-CodePudding

Using PostgreSQL, I have two tables (lunch_order & lunch_list). The two table is connected by lunch_id

What I want to do is to display a query that contain total count from each type of lunch for each day in a month

lunch_order

lunch_list

This is my current query syntax:

select  true_lunch_name as Lunch_Name, 
count(*) filter (where order_date = '2021-12-21' ) as day1,
count(*) filter (where order_date = '2021-12-22' ) as day2,
count(*) filter (where order_date = '2021-12-23' ) as day3
.....
from project1.lunch_order ord inner join  project1.lunch_list list on ord.lunch_type = list.lunch_id 
where order_date between '2021-12-21' and '2021-12-31' and list.is_active = 'true' and list.lunch_id != '1'
group by true_lunch_name,lunch_id order by lunch_id asc

This is the result with only 3 days selected for now:

But I was wondering, is there another ways to do it.

Because If I do it like this, I need to make 4 kind of queries for each type of months (28days, 29days, 30days, and 31days)

CodePudding user response：

This Single SQL will work for all months. This is for the current example. You can adjust the number and dates.

SQL:

SELECT lunch_name,
       Sum(day1) "day1",
       Sum(day2) "day2",
       Sum(day3) "day3",
       Sum(day4) "day4",
       Sum(day5) "day5",
       Sum(day6) "day6",
       Sum(day7) "day7",
       Sum(day8) "day8"
FROM   (SELECT lunch_name,
               CASE
                 WHEN Extract(day FROM order_date) = 21 THEN cnt
               END "day1",
               CASE
                 WHEN Extract(day FROM order_date) = 22 THEN cnt
               END "day2",
               CASE
                 WHEN Extract(day FROM order_date) = 23 THEN cnt
               END "day3",
               CASE
                 WHEN Extract(day FROM order_date) = 24 THEN cnt
               END "day4",
               CASE
                 WHEN Extract(day FROM order_date) = 25 THEN cnt
               END "day5",
               CASE
                 WHEN Extract(day FROM order_date) = 26 THEN cnt
               END "day6",
               CASE
                 WHEN Extract(day FROM order_date) = 27 THEN cnt
               END "day7",
               CASE
                 WHEN Extract(day FROM order_date) = 28 THEN cnt
               END "day8"
        FROM   (SELECT DISTINCT lunch_name,
                                order_date,
                                Count(lunch_name)
                                  OVER(
                                    partition BY lunch_name, order_date
                                    ORDER BY order_date) cnt
                FROM   lunch_order lo
                       INNER JOIN lunch_list ll
                               ON ll.lunch_id = lo.lunch_type) inline_view)
       final_inline_view
GROUP  BY lunch_name;

Output:

 lunch_name | day1 | day2 | day3 | day4 | day5 | day6 | day7 | day8
------------ ------ ------ ------ ------ ------ ------ ------ ------
 ccc        |      |      |    1 |    1 |      |      |    1 |    1
 bbb        |    2 |    1 |      |      |      |      |      |    1
(2 rows)

Setup:

create table lunch_order(lunch_type int , order_date date);
create table lunch_list(lunch_id int,lunch_name varchar(10));
insert into lunch_order values
,(20,'2021-12-22'),(22,'2021-12-23'),(22,'2021-12-24'),(22,'2021-12-27'),(20,'2021-12-28'),(22,'2021-12-28');
insert into lunch_list values(1,'aaa'),(20,'bbb'),(22,'ccc'),(23,'ddd'),(24,'eee');


postgres=# select * from lunch_order;
 lunch_type | order_date
------------ ------------
         20 | 2021-12-21
         20 | 2021-12-22
         22 | 2021-12-23
         22 | 2021-12-24
         22 | 2021-12-27
         22 | 2021-12-28
         20 | 2021-12-21
         20 | 2021-12-28
(8 rows)

postgres=# select * from lunch_list;
 lunch_id | lunch_name
---------- ------------
        1 | aaa
       20 | bbb
       22 | ccc
       23 | ddd
       24 | eee
(5 rows)