多个group by并使用mysql计算分组的行

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":

我需要这个,我们已经知道了这个类别,它们将是静态的,即“facebook”和“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

sqlfiddle链接:这里是小提琴

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.

如果有改进请,请回答或评论。

3 个解决方案

#1

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

嗨,我认为你应该使用SUM(CASE声明)。这里的锁定方式如下:

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

这是SQL小提琴

#2

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

#3

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

在mysql中的枢轴...看看这些:

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;

#1