SQL count returns error when GROUP BY clause
Why am I getting an error with this request on my real server but not on my localhost wamp?
SELECT
type as type,
COUNT(*) AS total
FROM page AS p
WHERE p.parent_id = p.page_id
error message,
SQLSTATE [42000]: Syntax error or access violation: 1140 Mixing GROUP columns (MIN (), MAX (), COUNT (), ...) without GROUP columns is illegal if there is no GROUP BY clause
How can I get around this?
my localhost returns this result which I need,
type total
page 16
source to share
When using an aggregate function such as COUNT
, you need to include GROUP BY
.
SELECT
type as type,
COUNT(*) AS total
FROM page AS p
WHERE p.parent_id = p.page_id
GROUP BY type
How it works locally, but not on your real server; MySql doesn't require a full enumeration of non-aggregate columns in the GROUP BY
default clause , but your real server might have ONLY_FULL_GROUP_BY
.
source to share