SQL/PHP code - Getting the sum of each months values

Question

These is a database which is formatted like this.

Date         Count
'2001-03-04'   4
'2001-03-05'   5
'2001-03-06'   4
'2001-03-08'   1
'2001-03-09'   3

This database goes on for 15 years or so. I need to display a rolling 12 month graph displaying counts and month. How would I find the sum of each month in a 12 month period.

iv tried that

$sql_months = "SELECT date(D.`created`) 'Date', count(*) 'Count' 
FROM  mytable
group by date(D.`created`)
where D.`created` >= DATEADD(month, -12, GETDATE())";
$result_months = $conn->query($sql_months);

How would I achieve this with php or sql code.

month count 8 12 11 06

etc for each month, 12 months backward from the current month.

Can you provide an example of the results that you want?

Gordon Linoff
– Gordon Linoff

2017-08-10 01:40:59 +00:00
Commented Aug 10, 2017 at 1:40 — Gordon Linoff
– Gordon Linoff, Commented Aug 10, 2017 at 1:40

Barmar · Accepted Answer · 2017-08-10 01:40:55Z

2

You need to group by month, not by date, and sum the counts rather than just counting them. And the WHERE clause goes before GROUP BY.

SELECT YEAR(created) AS year, MONTH(created) AS month, SUM(count) AS count
FROM mytable
WHERE created > DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY year, month

You need to group by both year and month, otherwise you'll combine the values from the current month a year ago with this month.

answered Aug 10, 2017 at 1:40

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL/PHP code - Getting the sum of each months values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related