Remove column from SELECT clause when using SUM in PostgreSQL-CodePudding

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

CodePudding user response：

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,