Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE restaurants (
  id int PRIMARY KEY,
  name text
);
CREATE TABLE food (
  id int PRIMARY KEY,
  restaurant_id int REFERENCES restaurants (id),
  waste_type_id smallint NOT NULL,
  product_id int NOT NULL,
  waste_kgs decimal NOT NULL, 
  prepared_kgs decimal NOT NULL,
  customers smallint NOT NULL,
  days date NOT NULL
);
CREATE TABLE tags (
  id int PRIMARY KEY,
  restaurant_id int REFERENCES restaurants (id),
  name text
);
INSERT INTO restaurants VALUES
(1, 'restaurant_1'),
(2, 'restaurant_2'),
(3, 'restaurant_3');
INSERT INTO food VALUES 
(1, 1, 1, 1, 1.7, 8.0, 96, '2023-1-1'),
(2, 1, 1, 10, 0.5, 7.0, 96, '2023-1-1'),
(3, 1, 1, 15, 0.3, 1.0, 96, '2023-1-1'),
(4, 2, 1, 12, 7.0, 0.8, 39, '2023-1-2'),
(5, 2, 1, 10, 1.1, 5.0, 39, '2023-1-2'),
(6, 2, 1, 11, 0.5, 1.5, 39, '2023-1-2'),
(7, 3, 1, 8, 10.0, 0.3, 97, '2023-1-3'),
(8, 3, 2, 17, 0.8, 0.0, 97, '2023-1-3'),
(9, 3, 1, 11, 0.5, 1.5, 39, '2023-1-3');
INSERT INTO tags VALUES
(1, 1, 'tag_1');

CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 3



INSERT 0 9



INSERT 0 1


-- I want rows with NULL values but I get 'SELECT 0'. 
SELECT 
  f.days AS days,
  SUM (f.waste_kgs) AS waste,
  SUM (f.prepared_kgs) AS prepared,
  f.waste_type_id AS waste_id,
  f.restaurant_id AS restaurant
FROM food f
INNER JOIN tags t
  ON f.restaurant_id = t.restaurant_id
WHERE t.name = ANY (array[]::text[])
  AND f.days BETWEEN '2000-1-1' and '2099-1-1'
  AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id


days
waste
prepared
waste_id
restaurant



SELECT 0


-- Here I get NULL values so I can separate the two result cases:
-- The first row is the result with the tags, NULL values because tag list is empty.
-- The second row is the result from all the restaurants without considering any tags.
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) 
  FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
  INNER JOIN tags t 
    ON t.restaurant_id = f.restaurant_id 
  WHERE t.name = ANY (array[]::text[])
-- Replace row 21 above with 
-- WHERE t.name = ANY (array['tag_1']::text[]) 
-- for a result row with restaurants having the 'tag_1' tag  
)
UNION ALL
SELECT Total, Total - Drinks AS "Without drinks", Drinks   
FROM (
  SELECT 
  SUM (f.prepared_kgs) 
  FILTER (
    WHERE f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Total,
  SUM (f.prepared_kgs)
  FILTER (
    WHERE (f.product_id = 10 OR f.product_id = 17) 
    AND f.days BETWEEN '2000-1-1' AND '2099-1-1'
    AND f.restaurant_id = ANY (array[1, 2, 3])
  ) AS Drinks
  FROM food f
);


total
Without drinks
drinks


null
null
null

25.1
13.1
12.0



SELECT 2


-- The whole query with an empty tag list. 
SELECT 
  f.days AS days,
  SUM (f.waste_kgs) AS waste,
  SUM (f.prepared_kgs) AS prepared,
  f.waste_type_id AS waste_id,
  f.restaurant_id AS restaurant
FROM food f
INNER JOIN tags t
  ON f.restaurant_id = t.restaurant_id
WHERE t.name = ANY (array[]::text[])
  AND f.days BETWEEN '2000-1-1' and '2099-1-1'
  AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id
UNION ALL 
SELECT
  f.days AS days,
  SUM (f.waste_kgs) AS waste,
  SUM (f.prepared_kgs) AS prepared,
  f.waste_type_id AS waste_id,
  f.restaurant_id AS restaurant
FROM food f
WHERE f.days BETWEEN '2000-1-1' and '2099-1-1'
  AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id;


days
waste
prepared
waste_id
restaurant


2023-01-01
2.5
16.0
1
1

2023-01-02
8.6
7.3
1
2

2023-01-03
10.5
1.8
1
3

2023-01-03
0.8
0.0
2
3



SELECT 4


-- The whole query with tag in the list.
SELECT 
  f.days AS days,
  SUM (f.waste_kgs) AS waste,
  SUM (f.prepared_kgs) AS prepared,
  f.waste_type_id AS waste_id,
  f.restaurant_id AS restaurant
FROM food f
INNER JOIN tags t
  ON f.restaurant_id = t.restaurant_id
WHERE t.name = ANY (array['basic']::text[])
  AND f.days BETWEEN '2000-1-1' and '2099-1-1'
  AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id
UNION ALL 
SELECT
  f.days AS days,
  SUM (f.waste_kgs) AS waste,
  SUM (f.prepared_kgs) AS prepared,
  f.waste_type_id AS waste_id,
  f.restaurant_id AS restaurant
FROM food f
WHERE f.days BETWEEN '2000-1-1' and '2099-1-1'
  AND f.restaurant_id = ANY (array[1, 2, 3])
GROUP BY days, restaurant, waste_id;


days
waste
prepared
waste_id
restaurant


2023-01-01
2.5
16.0
1
1

2023-01-02
8.6
7.3
1
2

2023-01-03
10.5
1.8
1
3

2023-01-03
0.8
0.0
2
3



SELECT 4
days	waste	prepared	waste_id	restaurant
2023-01-01	2.5	16.0	1	1
2023-01-02	8.6	7.3	1	2
2023-01-03	10.5	1.8	1	3
2023-01-03	0.8	0.0	2	3