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

must appear in the group by clause or be used in an aggregate function rails
sequelize column must appear in the group by clause or be used in an aggregate function
how to select a column not in group by clause in sql
must appear in the group by clause or be used in an aggregate function laravel
must appear in the group by clause or be used in an aggregate function django
column must be in the group by list
window function must appear in the group by clause or be used in an aggregate function;
error column users id must appear 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

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

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

Postgres error [column must appear in the GROUP BY clause or be , empname" must appear in the GROUP BY clause or be used in an aggregate function The only difference between two queries is that I am using  When you have a group by clause, in most systems you are restricted to only showing the fields in the group by clause in your select statement. Plus any aggregating columns using count (), sum (), etc. I ended up adding users.username to group by, which should have removed multiple posts with like users, but didn't.

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".

U118: Column must appear in the GROUP BY clause · pganalyze, U118: Column must appear in the GROUP BY clause. Explanation: This error occurs since you've specified an aggregate function (e.g. MAX() ) alongside a regular column in the query's target list, but you didn't specify the regular column as one of the columns to be grouped by. ERROR: column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname; so i do this. SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname; however this will not give the intented results, and the incorrect output below is shown.

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

ERROR: column must appear in the GROUP BY clause or be used , ERROR: column must appear in the GROUP BY clause or be used in an aggregate function Duration: 3:19 Posted: 13 Mar 2019 The cause of error: Column must appear in the GROUP BY clause or be used in an aggregate function The solution is to add the GROUP BY clause with column used in the select.

The love-hate relationship between SELECT and GROUP BY in SQL, ERROR: column “MY_TABLE.MY_COLUMN” must appear in the GROUP BY clause or be used in an aggregate function. I felt ashamed not  GROUP BY 1,2,3,4; [Code: 2640, SQL State: 42803] [Vertica]VJDBC ERROR: Column "a.Spend" must appear in the GROUP BY clause or be used in an aggregate function. If I take out " SUM(a.Spend) AS SpendTotal” and remove the GROUP BY then it works. Looking at documentation I believe GROUP BY is not used in analytic functions?

Error: Column <name> must appear in the GROUP BY clause or be , This article can help you troubleshoot the "column must appear in the GROUP BY clause or be used in an aggregate function" All columns that are not in an aggregate function MUST be in the group by clause if there is any column in an aggregate function. If you can get rid of all aggregation, then you won't have to have the group by, either. You could use a correlated subquery to get the most recent report date, rather than using max.

PostgreSQL GROUP BY, The GROUP BY clause must appear right after the FROM or WHERE clause. Following the GROUP BY clause is one column or a list of comma-separated columns. You can use the GROUP BY clause without applying an aggregate function. In a SELECT statement, the FROM clause and the WHERE clause (when used) must appear on separate lines. False After the word FROM in a SELECT statement, you list any conditions (restrictions) that apply to the data you want to retrieve.

Comments
  • yes, add l.location_name to the GROUP BY list, and notice that l.location.name != l.location_name. Refer : this
  • @cyberfly If adding location_name to the group by results in a different value, then a code can have more than one location_name. But you want to have a single code and a single location_name. So which location_name should be displayed? You might get any of them, and what's the use of that?
  • @BarbarosÖzhan actually this is simpler version of the real table that I want to query. Let see if in future I want to select more column, it does not make sense to keep adding the column into GROUP BY which will lead to different result
  • Thanks alternative view into the problem that I want to solve. This way everything can become easier such as want to paginate the records
  • Thanks for accepting, please do not forget to upvote as well. I would appreciate :)
  • Thanks good info, no wonder if I try to join using primary key there is no problem. Will upvote your answer because I cant change the table structure
  • this will bring undesired result, for example if I need to select more column
  • I modified the answer with a subquery. Hope it helps.
  • @TomYeh That subquery needs a LIMIT 1 else it will almost certainly return multiple results and raise an exception.
  • You're right. Basically it depends on your requirement to put more restricted condition in the where clause to get the one you want.