MySQL COUNT with GROUP BY shows multiple items



  • 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)



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2