mercredi 4 février 2015

GROUPING BY multiple columns/reqs


Say i have the following table



| type | date | count |
+-------------+--------------+-------------+
| cat | 2014-2-5 | 2 |
| horse | 2014-2-7 | 3 |
| cat | 2014-1-28 | 1 |
| dog | 2014-7-12 | 5 |
| cat | 2014-9-30 | 7 |
+-------------+--------------+-------------+


I want to see how much of each type was sold each month i.e



| type | Year | 2014Month=1 | 2014Month=2 |2014Month=7 |2014Month=9 |
+-------+-----------+-------------+-------------+------------+------------+
| cat | 2014 | 1 | 2 | 0 | 7 |
| horse | 2014 | 0 | 3 | 0 | 0 |
| dog | 2014 | 0 | 0 | 5 | 0 |
+-------+-----------+-------------+-------------+------------+------------+


So far the closest thing i could come up with is to do it one month at a time e.g. for February 2014:



SELECT type, YEAR(date), SUM(count) FROM myTable GROUP BY type WHERE date BETWEEN "2014-2-1" AND "2014-2-31";


And then manually rerun changing the dates for each month. There must be an easier way





Aucun commentaire:

Enregistrer un commentaire