CODEWITHSUNDEEP
mysqlsql
shekwo
shekwo

Reputation: 1447

How to get records for last 3 months while grouping by each month

Please how do I sum records in my MySQL table for the last 3 months but group them by month.

I want something like this:

select SUM(amount) from table where.....
group by month

I am doing the below but it is not returning any results

SELECT MONTHNAME(a.created_at) MONTH, YEAR(a.created_at) YEAR, SUM(a.credit) as credit, SUM(a.debit)
        FROM telco_transactions AS a
        WHERE a.telco_id = '1' and DATE(a.created_at) = DATE_ADD(NOW(), INTERVAL -3 MONTH)
        GROUP BY MONTHNAME(a.created_at), YEAR(a.created_at);

Upvotes: 3

Views: 1924

Answers (3)

shekwo
shekwo

Reputation: 1447

I found a answer

SELECT MONTHNAME(a.created_at) as tmonth, YEAR(a.created_at) as tYear,
        SUM(a.credit) as credit, SUM(a.debit)
        FROM telco_transactions AS a
        WHERE a.telco_id = '1' and DATE(a.created_at) BETWEEN DATE(NOW())-INTERVAL 3 MONTH AND DATE(NOW())
        GROUP BY MONTHNAME(a.created_at), YEAR(a.created_at);

Upvotes: 0

Rushil Ahuja
Rushil Ahuja

Reputation: 105

select T.date_time, SUM(T.amount) from (
    -> select * from TEST_TABLE where date_time >= DATE_ADD(CURDATE(), INTERVAL -3 MONTH) and date_time <= CURDATE())
    -> as T GROUP BY MONTH(T.date_time);

The table test_table looks like

+------------+--------+
| date_time  | amount |
+------------+--------+
| 2017-12-24 |     30 |
| 2017-09-24 |     30 |
| 2017-12-04 |     30 |
| 2017-11-24 |     30 |
| 2017-11-09 |     30 |
| 2017-10-24 |     30 |
+------------+--------+

and the output of the query looks like

+------------+---------------+
| date_time  | SUM(T.amount) |
+------------+---------------+
| 2017-09-24 |            30 |
| 2017-10-24 |            30 |
| 2017-11-24 |            60 |
| 2017-12-24 |            60 |
+------------+---------------+

Upvotes: 1

Rik
Rik

Reputation: 9

I would try this:

SELECT SUM(records) 
FROM TABLE_NAME
WHERE Date_Column >= DATEADD(MONTH, -3, GETDATE()) 
GROUP BY DATEPART(MONTH, Date_Column)

Upvotes: 0

Related Questions