CODEWITHSUNDEEP
sqlpostgresqlgroup-by
cyberfly
cyberfly

Reputation: 5888

Column must appeared in the GROUP BY clause or be used in an aggregate function

I have this query that will LEFT JOIN between two table and SUM the amount

SELECT
    l.code,
    SUM(p.amount)
FROM
    locations l
LEFT JOIN payments p 
ON p.location_code = l.code
GROUP by l.code

It working correctly and this is the output

enter image description here

Right now I want to select more column, example location name

SELECT
    l.code,
    l.location.name,
    SUM(p.amount)
FROM
    locations l
LEFT JOIN payments p 
ON p.location_code = l.code
GROUP by l.code

However I faced this error

Query 1 ERROR: ERROR:  column "l.location_name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 3:  l.location_name,

Adding location_name to GROUP BY its not an option if I need select more column in the future.

How should I modify my query to support more column select?

DB fiddle:

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=4485d0b1d37b8b476f3f513649a6ebfb

Upvotes: 0

Views: 843

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You can also define location.code as the primary key of the table (or declare it as unique). Then you can just aggregate by that column and select all other columns:

SELECT l.*, SUM(p.amount)
FROM locations l LEFT JOIN
     payments p 
     ON p.location_code = l.code
GROUP BY l.code;

Here is a db<>fiddle.

This is standard behavior for SQL; it is based on something called "functional dependence".

Upvotes: 1

S-Man
S-Man

Reputation: 23766

Maybe put the SUM() into a correlated subquery:

demo:db<>fiddle

SELECT
    l.*,
    (SELECT SUM(amount) FROM payments p WHERE p.location_code = l.code)
FROM
    locations l

Upvotes: 0

Tom Yeh
Tom Yeh

Reputation: 2017

How about this?

SELECT
    l.code,
    l.location_name,
    SUM(p.amount)
FROM
    locations l
LEFT JOIN payments p 
ON p.location_code = l.code
GROUP by l.code,l.location_name

If changing group by is not what you want, you might have to use subquery:

SELECT
    l.code,
    (select location_name from locations l2 where l.code=l2.code),
    SUM(p.amount)
FROM
    locations l
LEFT JOIN payments p 
ON p.location_code = l.code
GROUP by l.code

Upvotes: 0

Related Questions