How can I GROUP BY a single column in SQL?

Question

This is the rather basic query I tried:

SELECT cust_id, prod_id, total_amount as total_revenue
FROM sales
GROUP BY prod_id;

Trying to make an output like this:

 cust_id | prod_id | total_revenue
---------+---------+--------------
 1       | 1       |        574.32
 1       | 2       |         78.09
 1       | 3       |          8.48
 2       | 1       |        706.11
 2       | 2       |        104.03
 2       | 3       |         58.01

But I get,

ERROR:  column "sales.cust_id" must appear in the GROUP BY clause or be used in an aggregate function

If I add all columns to GROUP BY, the output looks like this:

 cust_id | prod_id | total_revenue 
---------+---------+------------
 1       | 1       |        2.11
 1       | 1       |        0.80
 2       | 1       |        2.50
 1       | 1       |        3.13
 2       | 1       |        1.55
 1       | 1       |        0.75
 1       | 1       |        2.03

... (405 rows)

Here the top answer that says this is a 'common aggregation problem' but I haven't been able to implement it.

This solution talks about an optimizer.

Yet another fix uses "With cte" and gives a long solution but this seems like this should be a simple query. Thanks for reading.

Answer 1

The problem you're having is due to the nature of the GROUP BY statement, when you use it you need to group all the fields that aren't part of the aggregate function (sum, max, min, etc) + one or more aggregate functions.

SELECT prod_id , cust_id, sum(total_amount) as total_revenue
FROM sales
GROUP BY prod_id, cust_id;

Answer 2

SELECT prod_id , cust_id, sum(total_amount) as total_revenue
FROM sales
GROUP BY 1,2;