CODEWITHSUNDEEP
mysqldate
Dmitry
Dmitry

Reputation: 505

MySQL - get data for time before some quarter AND year

I need to get MySQL results for users records with 'registered' date that happens BEFORE some quarter for some year.

For example to get data (users count, registered before Q3 2018) I do this request:

SELECT count(userid) AS value 
FROM users 
WHERE QUARTER(registered) <= "3" && YEAR(registered) <= "2018"

However this request give me not expected results (value much lower that it should be, looks like I get results only for Q3 for 2018 year, and without values for previous years with any quarters). If I remove quarter and check just data before Year - this works fine and show correct results for every year that I use.

Why this happens and how to correctly get value with Quarter?

Upvotes: 1

Views: 52

Answers (1)

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use the following solution:

SELECT COUNT(userid) AS value 
FROM users 
WHERE YEAR(registered) < 2018 OR (
    YEAR(registered) = 2018 AND QUARTER(registered) <= 3
)

With your current SELECT you get only all users which registered on third quarter or ealier on every year (2018 and earlier). But you can count all users of the years before 2018 (the quarter doesn't matter in this case) and all users of the current year but in the specified quarter or ealier.

Upvotes: 2

Related Questions