CODEWITHSUNDEEP
mysqlsql-server
Karimx
Karimx

Reputation: 83

How to get a SUM of grouped COUNT in SQL Query

I'm trying to get a SUM of a grouped Count.

my table result is like this.

ID | Name | count
1     k        5
2     k        4
3     a        12

I want a result lik this.

ID | Name | count
 1     k        9
 3     a        12

These is my real query.

SELECT COUNT( clubs_messages.id_user ) AS nbr, users.name, users.id, users.email
FROM users
JOIN clubs_messages ON users.id = clubs_messages.id_user
GROUP BY clubs_messages.id_user
UNION 
SELECT 'SUM' users.name, COUNT( clubs_messages.id_user ) 
FROM users
JOIN clubs_messages ON users.id = clubs_messages.id_user

So any help please. Thanks

Upvotes: 0

Views: 426

Answers (2)

geoandri
geoandri

Reputation: 2428

I think you just have to GROUP BY Name instead of user_id. Check the following query

   SELECT COUNT( clubs_messages.id_user ) AS nbr, users.name
   FROM users
   JOIN clubs_messages ON users.id = clubs_messages.id_user
   GROUP BY users.name

Upvotes: 3

Brian Driscoll
Brian Driscoll

Reputation: 19635

The issue here is that you are grouping by ID rather than by name:

SELECT COUNT( clubs_messages.id_user ) AS nbr, users.name, users.id, users.email
FROM users
JOIN clubs_messages ON users.id = clubs_messages.id_user
GROUP BY clubs_messages.id_user

To fix this, you'll need to group by name:

SELECT COUNT( clubs_messages.id_user ) AS nbr, users.name, users.id, users.email
FROM users
JOIN clubs_messages ON users.id = clubs_messages.id_user
GROUP BY users.name

Upvotes: 1

Related Questions