SQL count returns error when no GROUP BY clause

Question

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

don't see how it should work on 'localhost; what's the db server you are running locally? — , Mar 22 '12 at 02:56

Michael Fredrickson · Accepted Answer · 2012-03-22T02:57:39.270

15

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.

edited Mar 22 '12 at 02:57

answered Mar 22 '12 at 02:52

Michael Fredrickson

36,839
5
92
109

It helped me out of headache. Thanks! – Nấm Lùn Mar 29 '12 at 07:38

SQL count returns error when no GROUP BY clause

1 Answers1