Get sum of fields

Question

Possible Duplicate:
MySQL Count data for last 7 days

I have an issue where i need to show the sum of fields. Lets say I have a recordset 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

And i want to display an output which will show the sum of all the infections for each day as follows,

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

I used this query,

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

but it only gives an out put as follows which is NOT my requirement..

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

any help would be much helpful :) Thank you very much :) much appreciated :)

EDIT: Possible duplicate of : MySQL Count data for last 7 days

but not similar

well one infection can occur on one date and the same infection may not appear on the next day..thats why it is 0 n thank you for the reply — Hasitha Shan, Nov 05 '12 at 04:31
ohh yeaa..thank you for pointing me to that post..its somewhat the same :) — Hasitha Shan, Nov 05 '12 at 04:34
Except that it's more complicated than you need because you don't need missing *dates* filled in, only missing *infections*, do you? — AndreKR, Nov 05 '12 at 04:36
"select .......SUM( confidence )" . From where you got that confidence column???? — Fathah Rehman P, Nov 05 '12 at 04:37
yeaa i need missing dates and infection filled in as shown in the result i expect :) — Hasitha Shan, Nov 05 '12 at 04:39
This is related to your [Last question](http://stackoverflow.com/q/13151589/1369235). Note that you have used the fields in your query which are not available in your table. (like confidence, timeStamp etc) — Himanshu, Nov 05 '12 at 04:42
sorry for the confusion..i edited the question @FathahRehmanP — Hasitha Shan, Nov 05 '12 at 04:55

John Woo · Accepted Answer · 2012-11-05T04:42:18.727

SELECT e.*, COALESCE(SUM(d.`count`),0) `SUM of count`
FROM
(
  SELECT c.detectDate, a.infection
  FROM
    (
      SELECT  DISTINCT infection
      FROM    tbl_correlateddata
    ) a CROSS JOIN
    (
      SELECT  DISTINCT DATE(detectDate) detectDate 
      FROM    tbl_correlateddata 
    ) c
) e LEFT JOIN tbl_correlateddata d
    ON  DATE(d.detectDate) = e.detectDate AND
        d.infection = e.infection
 GROUP BY detectDate, infection
 ORDER BY e.detectDate, e.infection

SQLFiddle Demo

or

SELECT  DATE_FORMAT(e.detectDate, '%Y-%m-%d' ), 
        e.infection, 
        COALESCE(SUM(d.`count`),0) `SUM of count`
FROM
(
  SELECT c.detectDate, a.infection
  FROM
    (
      SELECT  DISTINCT infection
      FROM    tbl_correlateddata
    ) a CROSS JOIN
    (
      SELECT  DISTINCT DATE(detectDate) detectDate 
      FROM    tbl_correlateddata 
    ) c
) e LEFT JOIN tbl_correlateddata d
    ON  DATE(d.detectDate) = e.detectDate AND
        d.infection = e.infection
 GROUP BY e.detectDate, e.infection
 ORDER BY e.detectDate, e.infection

SQLFiddle Demo

as always.. thank you for the answer :) but small edit, i need the `SUM of count` field.. :) and its working, :) may i know how to get 0 for those that give `null` when you use null or do i have to do it via my backend application?? — Hasitha Shan, Nov 05 '12 at 04:44

Get sum of fields

1 Answers1