How to group and sum data by ID in table postgres-CodePudding

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'),

CodePudding user response：

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;

CodePudding user response：

You have to use Group BY and the sum will be done automatically

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' 
GROUP BY table1.product_id
ORDER by table1.product_id;

CodePudding user response：

I think just use GROUP BY function GROUP BY PRODUCT_ID without SUM function,total_num and total price will come aggregate.