Search code examples
phpmysqlsqlgroup-bymysql-error-1140

SQL count returns error when no GROUP BY clause

Why I get error with this query on my live server but not on my wamp localhost?

SELECT 
type as type, 
COUNT(*) AS total

FROM page AS p
WHERE p.parent_id = p.page_id

the error message is,

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

How can I get around to this?

my localhost return this result which is what I need,

type    total
page    16
Solution

  • When using an aggregate function, such as COUNT, you need to include a GROUP BY clause. 

    SELECT 
    type as type, 
    COUNT(*) AS total
FROM page AS p
WHERE p.parent_id = p.page_id
GROUP BY type

    As far as why this worked locally, but not on your live server; MySql doesn't require complete listing of non-aggregate columns in the GROUP BY clause by default, but your live server probably has the ONLY_FULL_GROUP_BY option turned on.