0

I have a query to get count of records in a table and getting below error :

ERROR:  column "emp.last_login_time" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...in') LIKE '%Delhi%')) ORDER BY emp.last_log...

Below is my query:

Select count(*) FROM Employee AS emp  
WHERE emp.last_login_time >= 0 
AND emp.last_login_time <= 1751060669383 
AND (((emp.employee_data->>'city') LIKE '%Delhi%' 
AND emp.employee_data->>'empID' is null)  
  OR ((emp.employee_data->>'employeeType') LIKE '%Permanent%' AND (emp.employee_data->>'city') LIKE '%Delhi%') 
  OR ((emp.employee_data->>'employeeType') IN ('Permanent Employee') AND (emp.employee_data->>'city') LIKE '%Delhi%')) 
ORDER BY emp.last_login_time DESC, emp.empID DESC LIMIT 100

Since this is a count query, I am not using a group by clause which is right. I am not sure why Postgres is still throwing this error. Can you someone pitch in?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
darecoder
  • 1,478
  • 2
  • 14
  • 29
  • Remove the order by clause (`ORDER BY emp.last_login_time DESC, emp.empID DESC`) and run the query again. See if that helps. – zedfoxus May 02 '22 at 03:50
  • @zedfoxus yes that works. But my question here is why? order by clause should not be a problem in a count query. – darecoder May 02 '22 at 03:59
  • I am assuming that by printing that error message, PG is saying: Did you mean you want group by instead of order by - are you trying to group something since I see a count(*)? When counting a stack of papers, ordering is counterproductive to counting. So, PG may be guiding you towards removing/replacing order by. This is just a guess. I don't know the internals of how PG parses the query. – zedfoxus May 02 '22 at 04:14
  • Does this answer your question? [PostgreSQL GROUP BY different from MySQL?](https://stackoverflow.com/questions/1769361/postgresql-group-by-different-from-mysql). Apparently, your query returns more than one row, which would mean that a GROUP BY would be required for the COUNT(). It's pretty hard to say, though, because you've failed to provide sample data and a [mre] that demonstrates the issue. See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952) for suggestions as to how to ask this sort of question. – Ken White May 02 '22 at 04:21

1 Answers1

0

Your query produces exactly one result row.

ORDER BY and LIMIT make no sense whatsoever. Drop those clauses.

Also, this filter:

OR ((emp.employee_data->>'employeeType') IN ('Permanent Employee') AND (emp.employee_data->>'city') LIKE '%Delhi%')

identifies a subset of this one:

OR ((emp.employee_data->>'employeeType') LIKE '%Permanent%'        AND (emp.employee_data->>'city') LIKE '%Delhi%')

So the first one is completely redundant and can be removed.
After more simplification your query burns down to:

SELECT count(*)
FROM   employee
WHERE  last_login_time BETWEEN 0 AND 1751060669383
AND    employee_data->>'city' LIKE '%Delhi%'
AND   (employee_data->>'empID' IS NULL OR
       employee_data->>'employeeType' LIKE '%Permanent%');

The technical reason behind the error message is simple: In a query with aggregate functions, you cannot order by an input column that has neither been aggregated nor grouped. That would be a logical contradiction. Related:

Finally, this is a non-sequitur:

Since this is a count query, I am not using a group by clause which is right.

You can use count() with GROUP BY to count per group.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228