Multiple group by and count the grouped rows using mysql

Question

I was having problems in creating counting rows by grouping based on a given field value. For example: I have a data structure like this:

+------+------------+
| id   | channel    |
+------+------------+
| 1    | "facebook" |
| 2    | "twitter"  |
| 3    | "facebook" |
| 2    | "facebook" |
| 4    | "twitter"  |
| 5    | "facebook" |
| 2    | "twitter"  |
| 1    | "facebook" |
| 2    | "twitter"  |
+------------+------+

And I need this, we already know the category, they will be static i.e."facebook" and "twitter":

+------+------------+------------+---------+
| id   | Facebook   |   Twitter  |  Total  |
+------+------------+------------+---------+
| 1    |     2      |      0     |    2    |
| 2    |     1      |      3     |    4    |
| 3    |     1      |      0     |    1    |
| 4    |     0      |      1     |    1    |
| 5    |     1      |      0     |    1    |
+------+------------+------------+---------+

sqlfiddle link: here is the fiddle

May not be the most elegant of answers but managed to come up with:

select user_id,user_name,
  count(case when channel = "twitter" then channel end) Twitter,
  count(case when channel = "facebook" then channel end) Facebook,
  count(case when channel in ("twitter","facebook") then channel end) Total
from april
group by user_id order by Total desc

If there improvements please, do answer or comment.

This looks like a standard solution. A modest performance benefit can usually be achieved by switching to the multiple LEFT JOIN approach, but I think what you've got looks neater. That said, this kind of issue (of data display) should really be handled in your application level code. — Strawberry, May 09 '15 at 08:31

score 2 · Answer 1 · answered May 09 '15 at 08:03

2

Hi i think you should use SUM(CASE statement). Here how that's could lock like:

SELECT id, SUM(CASE WHEN channel = 'facebook' THEN 1 ELSE 0 END) as facebook,
       SUM(CASE WHEN channel = 'twitter' THEN 1 ELSE 0 END) as twitter
FROM `data`
GROUP BY id

Here is SQL Fiddle for that

answered May 09 '15 at 08:03

Aleksandar Miladinovic

1,017
2
8
10

@Aleksander , ya i have already implemeted that, if you look at the update. Was looking for a more efficient answer, since there are a lot of counts and comaprison involved – mane May 09 '15 at 08:04
Oh sry didn't see that you edit question and add that... That's the first think that's crossed my mind for this problem... If I come with better idea I'll let you know :) GL – Aleksandar Miladinovic May 09 '15 at 08:21

score 0 · Accepted Answer · answered May 20 '15 at 10:56

select user_id,user_name,
  count(case when channel = "twitter" then channel end) Twitter,
  count(case when channel = "facebook" then channel end) Facebook,
  count(case when channel in ("twitter","facebook") then channel end) Total
from april
group by user_id order by Total desc

score -1 · Answer 3 · edited May 23 '17 at 11:44

-1

Pivots in mysql... Take a look on these:

Based on your fiddle (kudos for setting up that:-)):

SELECT  
  id,  
  COUNT(if(channel = "facebook", 1, NULL)) AS facebook, 
  COUNT(if(channel = "twitter", 1, NULL)) AS twitter, 
  COUNT(if(channel = "isntagram", 1, NULL)) AS instagram, 
  COUNT(if(channel = "microsite", 1, NULL)) AS microsite, 
  COUNT(if(channel = "kiosk", 1, NULL)) AS kiosk, 
  COUNT(*) As Total
FROM data 
GROUP BY id;

edited May 23 '17 at 11:44

Community

1
1

answered May 09 '15 at 07:18

Koshinae

2,240
2
30
40

I don't know if pivotal is able to solve my problem here, as I need to count according to channel, and the channels are fixed and not dynamic. – mane May 09 '15 at 07:30
@Koshinae, yes I understand the usage, but in my problem I need to count the grouped rows as well and fortunately the channel will that will appear in the rows are limited and known first hand. – mane May 09 '15 at 08:01
@mane, nah, no worries, just there are people who come by, doesn't understand the answer, does a -1, then goes away forever :-) – Koshinae May 10 '15 at 09:20

Multiple group by and count the grouped rows using mysql

3 Answers3