Clash Royale CLAN TAG#URR8PPP

MySQL Query with the count, group by

Table: statistics

id | user | Message
----------------------
1 | user1 |message1
2 | user2 |message2
3 | user1 |message3


I am able to find the count of messages sent by each user using this query.

select user, count(*) from statistics group by user;


How to show message column data along with the count? For example

user | count | message
------------------------
user1| 2 |message1
|message3
user2| 1 |message2


message3

user1

5 Answers
5

You seem to want to show Count by user, which message sent by user.

Count

user

message

user

If your mysql version didn't support window functions, you can do subquery to make row_number in select subquery, then only display rn=1 users and count

row_number

rn=1

CREATE TABLE T(
id INT,
user VARCHAR(50),
Message VARCHAR(100)
);


INSERT INTO T VALUES(1,'user1' ,'message1');
INSERT INTO T VALUES(2,'user2' ,'message2');
INSERT INTO T VALUES(3,'user1' ,'message3');


Query 1:

SELECT (case when rn = 1 then user else '' end) 'users',
(case when rn = 1 then cnt else '' end) 'count',
message
FROM (
select
t1.user,
t2.cnt,
t1.message,
(SELECT COUNT(*) from t tt WHERE tt.user = t1.user and t1.id >= tt.id) rn
from T t1
join (
select user, count(*) cnt
from T
group by user
) t2 on t1.user = t2.user
) t1
order by user,message


Results:

| users | count | message |
|-------|-------|----------|
| user1 | 2 | message1 |
| | | message3 |
| user2 | 1 | message2 |


select user, count(*) as 'total' , group_concat(message) from statistics group by user;


You could join the result of your group by with the full table (or vice versa)?

join

group by

Or, depending on what you want, you could use group_concat() using n as separator.

group_concat()

n

Use Group_concat

select user, count(0) as ct,group_concat(Message) from statistics group by user;


This will give you message in csv format

NOTE: GROUP_CONCAT has size limit of 1024 characters by default in mysql.

For UTF it goes to 1024/3 and utfmb4 255(1024/4).

You can use group_concat_max_len global variable to set its max length as per need but take into account memory considerations on production environment

SET group_concat_max_len=100000000


Update:

You can use any separator in group_concat

Group_concat(Message SEPARATOR '----')


Try grouping with self-join:

select s1.user, s2.cnt, s1.message
from statistics s1
join (
select user, count(*) cnt
from statistics
group by user
) s2 on s1.user = s2.user


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.