Fix must appear in the GROUP BY clause or be used in an aggregate function

1
votes
select
    case
        when daftar ='sd' then kouta
    end as a,
    case
        when daftar = 'smp' then kouta
    end as b,
    case
        when daftar = 'sma' then kouta
    end as c
from
    ajaran
GROUP BY
    tahun

and result error

[Err] ERROR: column "ajaran.daftar" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select case when daftar='sd' then kouta end as a,case when ...

I'm trying same query in MySQL and no error and success grouping how to use group by in PostgreSQL?

I want to group by tahun.

This is my table.

this my table

2
postgresql
MySQL is incorrect to let the query run without errors, but MySQL is less strict than PostgreSQL (and most other database systems, really). – Dai
What data are you expecting as output? Do you know what GROUP BY means? What is the meaning of tahun in this context and how does it relate to kouta? – Dai

2 Answers

0
votes

Presumably, you want sum() or max() as in:

select tahun,
       max(case when daftar = 'sd' then kouta end) as a,
       max(case when daftar = 'smp' then kouta end) as b,
       max(case when daftar = 'sma' then kouta end) as c
from ajaran
group by tahun;

I think the error is pretty clear . . . daftar and kouta are neither keys in the group by nor are they in aggregation functions in your query. Which aggregation is most appropriate is not clear without further guidance, such as sample data and desired results.

0
votes

When grouping, you must return one row per group, but your select doesn't aggregate the values into one row.

Provide an aggregate, for example using max():

select
    tahun,
    max(case when daftar ='sd' then kouta end) a,
    max(case when daftar = 'smp' then kouta end) b,
    max(case when daftar = 'sma' then kouta end) c
from ajaran
group by tahun