CODEWITHSUNDEEP
mysqldatetime
James Ramputh
James Ramputh

Reputation: 77

Return all records within 3 month period (MySQL)

So what I want to achieve is to return data from within the last 3 months. My current MySQL query is as follows:

SELECT MONTH(service_date_time) AS month, SUM(service_price) AS total FROM appointments WHERE user_id = 1 AND service_date_time >= last_day(now()) + INTERVAL 1 day - INTERVAL 3 month GROUP BY YEAR(service_date_time), MONTH(service_date_time)

My table appointments contains data from January and May and service_date_time is a date_time field.

The problem I'm having is that it returns 2 rows, one row totaling the price for January and one row totaling the price for May. The row for May shouldn't be returned since it is not within the past three months.

Anyone have an idea why?

Upvotes: 0

Views: 135

Answers (1)

ahmad
ahmad

Reputation: 2729

You are requesting all records that are greater than the given date, If you want all the records up to now you'll have to ask for a range for example:

WHERE service_date_time BETWEEN (LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 3 MONTH) AND NOW()

this would limit the records & give you from 3 months ago till now

Upvotes: 1

Related Questions