MySQL GROUP BY计数 [英] MySQL GROUP By count

问题描述

ID| FROM |  TO   |     Type    |
--|------|-------|-------------|
1 | Brij |Nilesh |   Call_In   |
--|------|-------|-------------|
2 |Jaydip| Vipul |   Call_In   |
--|------|-------|-------------|  
3 |Rajesh|Nilesh |   Call_In   |
--|------|-------|-------------|
4 |Jaydip| vipul |   Call_Out  |
--|------|-------|-------------|
5 |Jaydip| vipul |   Call_In   |
--|------|-------|-------------|
6 | Brij | Nilesh|   Call_Out  |
--|------|-------|-------------|
7 |vipul | Brij  |   Call_In   |

所以我曾经使用此查询从数据中获得最频繁的配对

So i used to get most frequent pair from data using this query

Select FROM,TO, COUNT('FROM' & 'TO') AS howmany
from table
GROUP BY FROM,TO

这给了我这样的结果

  FROM  |   TO   |howmany|
--------|--------|-------|
  Brij  | Nilesh |   2   |
--------|--------|-------|
 Jaydip | Vipul  |   3   |
--------|--------|-------|
 Rajesh | Nilesh |   1   |
--------|--------|-------|
 vipul  | Brij   |   1   |

但是我也想要这些组中的呼叫类型总和

But i also want the sum of type of calls In these group like

Brij  | Nilesh |howmany| Call_In | Call_Out |
------|--------|-------|---------|----------|
Jaydip| vipul  |   3   |    2    |    1     |
------|--------|-------|---------|----------|
Brij  | Nilesh |   2   |    1    |    1     |
------|--------|-------|---------|----------|
Rajesh| Nilesh |   1   |    1    |    0     |
------|--------|-------|---------|----------|
vipul | Brij   |   1   |    1    |    0     |

所以请建议我查询

推荐答案

SELECT FROM, TO, COUNT(*) AS howmany,
       SUM(Type = 'Call_In') AS Call_In, SUM(Type = 'Call_Out') AS Call_Out
FROM table
GROUP BY FROM, TO

这篇关于MySQL GROUP BY计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！