Alex Davies Alex Davies - 15 days ago 9
MySQL Question

SQL STATMENT [42000] Error

I try the following SQL STATMENT 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

Answer

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