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)
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
over() and without limit the count in the rows are generally 1. When using over() every count in the rows are 45.
SELECT COUNT(DISTINCT activities.id) FROM activities ;
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 ;
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.
activities.idvalues. How can I use GROUP BY but still only get one count result? What is desired result?8? useGROUP BY NULL...activities.idcolumn 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.