SQL STATEMENT [42000] Error

Question

I try the following SQL STATEMENT QUERY :

SELECT COUNT(status) as tot,
       CASE WHEN (status = 'pending')
       THEN COUNT(status) ELSE 0 END pct,

       CASE WHEN (status = 'rejected')
       THEN COUNT(status) ELSE 0 END rct,

      CASE WHEN (status = 'active')
      THEN COUNT(status) ELSE 0 END act,

      CASE WHEN (status = 'disabled')
      THEN COUNT(status) ELSE 0 END dct

     FROM committees

What wrong with the query above because I get the following error below.

The error I get

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #2 of SELECT list contains nonaggregated column 'house.committees.status'; this is incompatible with sql_mode=only_full_group_by

Am using PDO and my sql version is 5.7.14 Though I just upgraded from 5.6.17

SUM is aggregate function and in the only_full_group_by Mode you use a group by. Set the SQL_MODE to '' or use group by status — Bernd Buffen

Akshey Bhat Akshey Bhat · Accepted Answer · 2016-11-25T10:53:07

You need GROUP BY clause in your query if you want to calculate count for a particular committee. If you want to calculate overall count use the below query. This query doesn't use GROUP BY. Also the reason for the error is that you can't have a non-aggregated column in a select query with aggregation functions. In case you want to add non-aggregated column in select query, it should be part of GROUP BY clause.

SELECT COUNT(status) as tot,
           SUM(CASE WHEN status = 'pending'
           THEN 1 ELSE 0 END) as pct,

           SUM(CASE WHEN status = 'rejected'
           THEN 1 ELSE 0 END) as rct,
          SUM(CASE WHEN status = 'active'
          THEN 1 ELSE 0 END) as act,

          SUM(CASE WHEN status = 'disabled'
          THEN 1 ELSE 0 END) as dct

         FROM committees

SQL STATEMENT [42000] Error

1 Answers