Aggregation with required columns in GROUP BY clause in postgresSQL

Question

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?

[Group by clause in mySQL and postgreSQL, why the error in postgreSQL?](https://stackoverflow.com/questions/33629168/group-by-clause-in-mysql-and-postgresql-why-the-error-in-postgresql) — Lukasz Szozda, Apr 29 '21 at 13:18
[db<>fiddle demo MariaDB with ONLY_FULL_GROUP_BY](https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=02d6c391d57bc6a27837beb459c5f511) => Same error: "s.sales_channel' isn't in GROUP BY" — Lukasz Szozda, Apr 29 '21 at 13:21
Please explain the logic you want to implement. It is not clear. — Gordon Linoff, Apr 29 '21 at 13:39

score 0 · Accepted Answer · answered Apr 29 '21 at 13:16

0

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

answered Apr 29 '21 at 13:16

Jeremy

6,313
17
20

For my own documenation: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=0c1edf0f800ea310eda4acda315acfbd – Michi Apr 29 '21 at 13:38

score 0 · Answer 2 · answered Apr 29 '21 at 13:41

You may want the most common product. SYou can get this with two levels of aggregation:

SELECT s.campaign AS campaign, s.sales_channel AS sales_channel,
       MAX(s.product) FILTER (WHERE seqnum = 1) AS mode_product,
       SUM(s.quantity) AS quantity
FROM (SELECT campaign, sales_channel, product, SUM(quantity) as quantity,
             ROW_NUMBER() OVER (PARTITION BY campaign, sales_channel ORDER BY SUM(quantity) DESC) as seqnum
      FROM sales s
      GROUP BY 1, 2, 3
     ) s
GROUP BY 1, 2
ORDER BY s.campaign;

Here is a db<>fiddle.

Aggregation with required columns in GROUP BY clause in postgresSQL

2 Answers2