How to solve SQL Error: 'column must appear in the GROUP BY clause or be used in an aggregate function

Question

I'm creating a SQL query to get some records from my database and the total of some of these grouping by the ID, but i have a problem

SELECT codempresa,nombreevaluado,horaprogramada,count(*) AS count 
FROM recepcion_agenda 
WHERE asistencia = false 
GROUP BY codempresa 
ORDER BY codempresa, nombreevaluado, horaprogramada;

i expect the following result

codempresa | nombreevaluado| horaprogramada| count
1          |  luis         |   12:12       |  2
1          |  jack         |   21:21       |  2
2          |  mick         |   09:02       |  1

Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) You could figure this out if you read the words of the message. *Column must appear in the GROUP BY or be used in an aggregate function* is pretty clear. You have four columns in your SELECT. One is an aggregate (the last). Only one of the other three is in your GROUP BY. Think about the message and my last sentence. — Ken White, Jun 17 '19 at 23:55

score 0 · Answer 1 · answered Jun 17 '19 at 23:45

0

nombreevaluado and horaprogramada must be included in the group by list so long as they exist in the select list.

answered Jun 17 '19 at 23:45

snickholas

51
4

score 0 · Accepted Answer · answered Jun 18 '19 at 00:14

To fix the error, you can include the columns in the GROUP BY:

SELECT codempresa, nombreevaluado, horaprogramada,
       count(*) AS count 
FROM recepcion_agenda 
WHERE NOT asistencia
GROUP BY codempresa, nombreevaluado, horaprogramada
ORDER BY codempresa, nombreevaluado, horaprogramada;

The columns in the GROUP BY determine the rows in the result set -- one row per unique combination of values. Your sample results show "1" for codempresa on two rows, so you seem to want a combination of the three columns to define each row.

How to solve SQL Error: 'column must appear in the GROUP BY clause or be used in an aggregate function

2 Answers2