获取按日期和另一个字段分组的字段总数 [英] Get total of a field grouped by date and another field

问题描述

我有一个数据表如下，

  detectDate | isp | infection |计数
 -------------------------------------- 
 2012-10 -02 01：00 | aaaa |恶意软件| 3 
 2012-10-02 01：30 | bbbb |恶意软件| 2 
 2012-10-02 01：33 | bbbb | spy-eye | 2 
 2012-10-02 01:45 | aaaa | DDos | 1 
 2012-10-03 01:50 | cccc | malware | 2 
 2012-10-03 02:00 | dddd | TDSS | 2 
 2012-10-03 04:50 | dddd | TDSS | 3 
  

我需要像这样得到一个输出，按照日期和感染分组，如此，

  detectDate | infection |计数
 -------------------------------------- 
 2012-10 -02 |恶意软件| 5 
 2012-10-02 |间谍眼| 2 
 2012-10-02 | DDos | 1 
 2012-10-03 |恶意软件| 2 
 2012-10-03 | TDSS | 5 
  

我知道如何使用group，但不是他远:(可以请你帮我，我需要按日期和感染进行分组，并获得所示计数字段的总数。

p>使用 SUM 函数来总结 count 列并使用 DATE_FORMAT 函数按日期进行分组。 p>

  SELECT DATE_FORMAT（detectDate，'％Y-％m-％d'）AS detectDate 
，infection 
 ，SUM（`COUNT`）作为`count` 
 FROM myTable 
 G ROUP BY DATE_FORMAT（detectDate，'％Y-％m-％d'），感染
  

看到这个SQLFiddle

I have a table with data as follows,

detectDate      |isp    |infection  | count
--------------------------------------
2012-10-02 01:00|aaaa   |malware    |3
2012-10-02 01:30|bbbb   |malware    |2
2012-10-02 01:33|bbbb   |spy-eye    |2
2012-10-02 01:45|aaaa   |DDos       |1
2012-10-03 01:50|cccc   |malware    |2
2012-10-03 02:00|dddd   |TDSS       |2
2012-10-03 04:50|dddd   |TDSS       |3

I need to get an out put like this, grouped by date and infection like so,

detectDate      |infection  | count
--------------------------------------
2012-10-02      |malware    |5
2012-10-02      |spy-eye    |2
2012-10-02      |DDos       |1
2012-10-03      |malware    |2
2012-10-03      |TDSS       |5

I know how to use group in general but not his far :( can you please help me I need to group by date and infection and get the total of the count field as shown.

解决方案

Use SUM function to sum count column and use DATE_FORMAT function to group by only date.

SELECT DATE_FORMAT(detectDate, '%Y-%m-%d') AS detectDate
      ,infection 
      ,SUM(`COUNT`) as `count`
FROM myTable
GROUP BY DATE_FORMAT(detectDate, '%Y-%m-%d'), infection

See this SQLFiddle

这篇关于获取按日期和另一个字段分组的字段总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！