MySQL COUNT with GROUP BY shows multiple items

Question

Below gives one item but the wrong count

SELECT COUNT(activities.id) FROM activities AS COUNT

[0] => stdClass Object
(
 [COUNT] => 189
)

Below gives multiple items but the correct count (total array items)

SELECT COUNT(activities.id) AS COUNT
GROUP BY activities.id

[0] => stdClass Object
(
 [COUNT] => 4
)
[1] => stdClass Object
(
 [COUNT] => 4
)

How can I use GROUP BY but still only get one count result? (I've tried DISTINCT without luck)

• Akina: These 2 rows provides the amounts for 2 different activities.id values. How can I use GROUP BY but still only get one count result? What is desired result? 8? use GROUP BY NULL...
• mustaccio: Please consider following these suggestions.
• Jens Törnell: @Akina GROUP BY activities.id will limit my result from 189 to 45. My desired result is 45 but I don't get there. When using GROUP BY NULL it will return 189, the same as not using GROUP BY at all.
• Akina: Add activities.id column into the output list of your query with GROUP BY. Show the output, or at least tell what is this column value in the row where COUNT() is equal to 45.
• Jens Törnell: @Akina I've added a working answer. It works like expected but it may have some flaws when it comes to performance, not sure.

Answer 1

This works but I'm not sure it's the best in terms of performance:

SELECT COUNT(activities.id) over() AS COUNT
FROM activities
GROUP BY activities.id
LIMIT 1

• Akina: This is obviously incorrect while taking into account your comments under the question. You tell: "GROUP BY activities.id will limit my result from 189 to 45. My desired result is 45". This means that the output contains some rows, and at least in one row the value for aggregated column is NOT equal to 45. The query does not use ORDER BY - hence it is possible that sometimes the query will return the value other than 45 which you need - i.e. the query is not deterministic. Hence this query is incorrect.
• Jens Törnell: @Akina Not really. Without over() and without limit the count in the rows are generally 1. When using over() every count in the rows are 45.
• ypercubeᵀᴹ: This works but you could have used the simpler: SELECT COUNT(DISTINCT activities.id) FROM activities ;

Answer 2

You can use the simpler (than the COUNT() OVER () and LIMIT 1 approach):

SELECT COUNT(DISTINCT a.id) -- AS count_distinct_activities_id
FROM activities AS a ;

Answer 3

Something like

SELECT activities.id, COUNT(1) GROUP BY activities.id

Since you aren't giving it content in your select worth grouping by you should have no reason to group by anything at all. Your select suggests the same outcome as: SELECT COUNT(activities.id)

obviously that would be your total and not your per activity.

would give you each activity id along with how many times it appears in your dataset. However, it's hard to validate without knowing what your dataset actually contains.

Also I would suggest not using keywords as column names as it's generally frowned upon.

• Jens Törnell: That did not work. It shows multiple rows. I've added an own answer. It may have flaws but at least it works.