0

I'm stuck in a place in my current project. The problem is that I have a table called 'deal' and it has three fields.

id |     date   |  deal_value|
------------------------------
 1 | 2014-01-03 |     200    |
 2 | 2014-01-03 |     40     |
 3 | 2014-02-20 |     23     |
 4 | 2014-03-21 |     440    |
 5 | 2014-06-18 |     256    |
 6 | 2014-06-03 |     55     |
 7 | 2014-12-15 |     456    |

Now the thing is that I need a sql query to get result as follows.

|  month  |  deal_value(count) |
--------------------------------
|   01    |    240             |
|   02    |    23              |
|   03    |    440             |
|   04    |    0               |
|   05    |    0               |
|   06    |    311             |
|   07    |    0               |
|   08    |    0               |
|   09    |    0               |
|   10    |    0               |
|   11    |    0               |
|   12    |    456             |

please if anyone can, put some idea...

kalanamw
  • 5
  • 5
  • 1
    `COUNT` & `GROUP BY MONTH()` http://stackoverflow.com/questions/508791/mysql-query-group-by-day-month-year – SubjectCurio Jun 20 '14 at 12:18
  • @MLeFevre I think the hard part for the OP is having the 0's show up since there are no matching rows. That's not in what you linked to. –  Jun 20 '14 at 12:20
  • 2
    @JeremyMiller I'm sure a seperate 5 second search could help him with that as well http://stackoverflow.com/questions/8007363/mysql-group-by-month-including-empty-months – SubjectCurio Jun 20 '14 at 12:22
  • @MLeFevre I agree. Glad you have all that time on your hands, lol. –  Jun 20 '14 at 12:23

2 Answers2

0

Here is a quick and dirty way to do it.

SELECT a.mnth, COALESCE(SUM(deal_value),0) FROM 
    (SELECT 1 mnth UNION SELECT 2 UNION SELECT 3 
     UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
     UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 
     UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) a
LEFT OUTER JOIN deal
ON a.mnth=MONTH(`date`)
GROUP BY a.mnth

This don't need another table, uses SELECT .. UNION to generate a list from 1 to 12 and LEFT OUTER JOIN the original data.

Demo: SQL Fiddle

bansi
  • 55,591
  • 6
  • 41
  • 52
0

This is working fine....

SELECT  distinct DATE_FORMAT(a.date, '%Y-%m') as date,
        (select sum(deal_value) from deal as a1 where DATE_FORMAT(a1.date, '%Y-%m')=DATE_FORMAT(a.date, '%Y-%m') ) as sum
FROM `deal` as a
Mani
  • 2,675
  • 2
  • 20
  • 42