Remove column from SELECT clause when using SUM in PostgreSQL

Question

I checked the answer here but didn't solve my problem because I even adding the else still throwing the same error:

ERROR:  column table1.column11 must appear in the GROUP BY clause or be used in an aggregate function

I would like to remove table1.column11 from SELECT because is affecting the GROUP BY. Instead of each ENUM type be a column in a single line this query below is returning a table with a ENUM column filled in different lines.

Query example:

SELECT table1.column11, -- enum column
   table2.id,
   (case when table1.column11 = 'enum_value_1' then SUM(table1.column12) end) as enum_value_1,
   (case when table1.column11 = 'enum_value_2' then SUM(table1.column12) end) as enum_value_2,
   --- ...
   (case when table1.column11 = 'enum_value_9' then SUM(table1.column19) end) as enum_value_9,
FROM table1
LEFT JOIN table2 ON table2.id = table1.table2_id
WHERE table1.column13 IS NULL
GROUP BY table2.id, table1.column11
ORDER BY table2.id ASC

How is now:

table1.column11 | table2.id | enum_value_1 | enum_value_2 | ... | enum_value_9
================|===========|==============|==============|=====|=============
enum_value_1    | 1         | 10           | [null]       | ... | [null]
enum_value_2    | 1         | [null]       | 20           | ... | [null]
...             | ...       | ...          | ...          | ... | ...
enum_value_9    | 1         | [null]       | [null]       | ... | 15

How I would like to have:

table2.id | enum_value_1 | enum_value_2 | ... | enum_value_9
==========|==============|==============|=====|=============
1         | 10           | 20           | ... | 15

@klim when you have time :) pls

Marcelo Idemax
– Marcelo Idemax

2022-04-20 15:31:53 +00:00
Commented Apr 20, 2022 at 15:31 — Marcelo Idemax
– Marcelo Idemax, Commented Apr 20, 2022 at 15:31

klin · Accepted Answer · 2022-04-20 19:08:05Z

1

The example in the question is difficult to reproduce, but I think my suggestions will be helpful. Remove table1.column11 from the select list and use the column only inside the aggregates.

So instead of:

SELECT table1.column11, -- enum column
   table2.id,
   (case when table1.column11 = 'enum_value_1' then SUM(table1.column12) end) as enum_value_1,

use:

SELECT
   table2.id,
   sum(case when table1.column11 = 'enum_value_1' then table1.column12 end) as enum_value_1,

answered Apr 20, 2022 at 19:08

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Marcelo Idemax Over a year ago

thanks @kiln worked just fine

Collectives™ on Stack Overflow

Remove column from SELECT clause when using SUM in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related