0

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)

8
  • 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...
    – Akina
    May 26, 2022 at 13:54
  • Please consider following these suggestions.
    – mustaccio
    May 26, 2022 at 13:59
  • @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. May 26, 2022 at 14:00
  • 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.
    – Akina
    May 26, 2022 at 17:09
  • @Akina I've added a working answer. It works like expected but it may have some flaws when it comes to performance, not sure. May 26, 2022 at 17:22

3 Answers 3

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
3
  • 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.
    – Akina
    May 26, 2022 at 17:27
  • 2
    @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. May 26, 2022 at 17:36
  • This works but you could have used the simpler: SELECT COUNT(DISTINCT activities.id) FROM activities ; Sep 29, 2022 at 8:36
1

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 ;
0

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.

1
  • That did not work. It shows multiple rows. I've added an own answer. It may have flaws but at least it works. May 26, 2022 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.