Piyush Piyush - 2 months ago 9
MySQL Question

Apply group by and limit offset together in MySQL

I have a table name

transactions
where data rows are stored with a date

Like

`trans_id` `amount` `tdate`


I want to filter that data like last 30 days, last 31st 60 days, last 61-90 days calculate the overall amount also

My queries are

For last 30 days

SELECT SUM(amount) AS amt FROM transactions GROUP BY DATE(tdate) ORDER BY DATE(tdate) DESC LIMIT 30


Working fine and show SUM of amount (last 30days)

But for last 31-60 days not working

SELECT SUM(amount) AS amt FROM transactions GROUP BY DATE(tdate) ORDER BY DATE(tdate) DESC LIMIT 60,31


How to solve it ? I do want to include only 31 to 60 days amount only

Answer

Use the following: (It returns data between today and last 30 days)

SELECT  DATE_FORMAT(DateCol, '%m/%d/%Y')
FROM Table
WHERE DateCol BETWEEN CURDATE() - INTERVAL
30 DAY AND CURDATE()

Or more precisely, this will do the trick:

DateCol BETWEEN (NOW() - INTERVAL 60 DAY) 
AND (NOW() - INTERVAL 30 DAY)

CURDATE() works for only date portion. If you have DateTime column, then NOW() will do.