How to group and sum data by ID in table postgres

July 27, 2022

I’m trying to calculate summary cost of products by it’s id in every month.
I’ve got such query and result :

SELECT to_char(table2.order_date,'MM-YYYY'),
table4.name AS category_name,
table3.name AS product_name,
table1.product_id,
table1.product_num AS total_num,
table1.product_num * table3.price AS total_price 
FROM order_products AS table1 
JOIN orders AS table2 ON table1.order_id = table2.id 
JOIN products AS table3 ON table1.product_id = table3.id 
JOIN product_categories AS table4 ON table3.category_id = table4.id 
WHERE to_char(table2.order_date,'MM-YYYY') = '04-2022' 
ORDER by table1.product_id;

How to group by product_id and sum total_num, total_price?

UPD:

When i just use GROUP by table1.product_id I have an error:

ERROR: ОШИБКА: столбец "table2.order_date" должен фигурировать в
предложении GROUP BY или использоваться в агрегатной функции LINE 1:
SELECT to_char(table2.order_date,’MM-YYYY’),

>Solution :

You can apply max/min to all the rest repeating values

SELECT 
  max(to_char(table2.order_date,'MM-YYYY')),
  max(table4.name) AS category_name,
  max(table3.name) AS product_name,
  table1.product_id,
  sum(table1.product_num) AS total_num,
  sum(table1.product_num * table3.price) AS total_price 
FROM order_products AS table1 
JOIN orders AS table2 ON table1.order_id = table2.id 
JOIN products AS table3 ON table1.product_id = table3.id 
JOIN product_categories AS table4 ON table3.category_id = table4.id 
WHERE to_char(table2.order_date,'MM-YYYY') = '04-2022' 
GROUP BY table1.product_id
ORDER by table1.product_id;