MySQL `SUM` with `GROUP BY` is missing data in results

Question

I have a database with a table containing information on some images, each row containing a createdAt date and a viewCount. The data ranges from September 2014 until today (July 2016). I want to get a monthly sum of the amount of views across all images for the month

When I run the query

SELECT YEAR(createdAt), MONTH(createdAt), SUM(viewCount) 
FROM Images 
GROUP BY MONTH(createdAt);

I'm only returned 12 rows with results between September 2014 and August 2015

Year | Month | Views
------------------- 
2014 |  9    | 1452 
2014 |  10   | 279 
2014 |  11   | 34428
2014 |  12   | 4763 
2015 |  1    | 2826 
2015 |  2    | 777 
2015 |  3    | 568 
2015 |  4    | 1309 
2015 |  5    | 46744 
2015 |  6    | 1541 
2015 |  7    | 8160 
2015 |  8    | 91

If I add a date restraint it will give me the latest data, but again only 12 rows

SELECT YEAR(createdAt), MONTH(createdAt), SUM(viewCount) 
FROM Images WHERE createdAt > DATE('2015-08-01 00:00:00')
GROUP BY MONTH(createdAt);

Year | Month | Views
--------------------
2015 |  8   | 981
2015 |  9   | 1031
2015 |  10  | 2566
2015 |  11  | 3325
2015 |  12  | 411    
2016 |  1   | 2140
2016 |  2   | 710
2016 |  3   | 714
2016 |  4   | 1985
2016 |  5   | 426
2016 |  6   | 119
2016 |  7   | 81

I do realise that since it's July the second query stops there as that's where the data ends, but why does the first query not return all the results?

Judging by what it appears you want, try also grouping by `YEAR(createdAt)` as well in your first query. — Matt Cremeens, Jul 12 '16 at 16:55

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

Group by year/month:

SELECT YEAR(createdAt), MONTH(createdAt), SUM(viewCount) 
FROM Images 
--WHERE createdAt > DATE('2015-08-01 00:00:00')
GROUP BY YEAR(createdAt), MONTH(createdAt);

Related Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

Keep in mind that from MySQL 5.7.6+ your initial query may not even work because of only_full_group_by which is set by default.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 12 '16 at 16:54

Lukasz Szozda

162,964
23
234
275

That gave me all the results (removing the WHERE clause of course) – Ashok Fernandez Jul 12 '16 at 16:56

score 1 · Answer 2 · answered Jul 12 '16 at 16:56

1

You can simply add Year to you group by

 SELECT YEAR(createdAt), MONTH(createdAt), SUM(viewCount) 
 FROM Images 
 GROUP BY YEAR(createdAt), MONTH(createdAt)
 ORDER BY YEAR(createdAt), MONTH(createdAt)

answered Jul 12 '16 at 16:56

ScaisEdge

131,976
10
91
107

MySQL `SUM` with `GROUP BY` is missing data in results

2 Answers2