Aggregation with required columns in GROUP BY clause in postgresSQL

DB-Fiddle

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    campaign VARCHAR,
    sales_channel VARCHAR,
    product VARCHAR,
    quantity DECIMAL
);

INSERT INTO sales
(campaign,  sales_channel, product, quantity)
VALUES 
('C001', 'online', 'product_04', '800'),
('C001', 'online', 'product_03', '300'),
('C001', 'online', 'product_02', '200'),

('C002', 'retail', 'product_05', '500'),
('C002', 'retail', 'product_04', '600'),
('C002', 'retail', 'product_03', '600'),
('C002', 'retail', 'product_03', '600'),
('C002', 'retail', 'product_02', '600'),

('C003', 'fair', 'product_07', '600'),
('C003', 'fair', 'product_01', '400');

Expected Result:

campaign |  sales_channel |   product     |     quantity  |
---------|----------------|---------------|---------------|---
C001     |      online    |   product_04  |     1.300     |
C002     |      retail    |   product_05  |     2.900     |
C003     |      fair      |   product_07  |     1.000     |

---

To get the expected result I tried to go wiht this query:

SELECT 
s.campaign AS campaign,
s.sales_channel AS sales_channel,
s.product AS product,
SUM(s.quantity) AS quantity
FROM sales s
GROUP BY 1;

However, it gives me error: column s.sales_channe" must appear in the GROUP BY clause.
The problem is when I add the sales_channel to the GROUP BY clause I am not able to ge the expected result because then it groups both by campaign and sales_channel.

How do I have to modify the query to get the expected result as in MariaDB?

Answer

You have to aggregate sales_channel and product in some way. For sales_channel, it doesn’t really matter how you aggregate it, because there’s only one option per campaign. Product, on the other hand, has multiple options. It appears that you want the maximum product, so choose that:

SELECT 
s.campaign AS campaign,
max(s.sales_channel) AS sales_channel,
max(s.product) AS product,
SUM(s.quantity) AS quantity
FROM sales s
GROUP BY 1;

Fiddle