FERNANDO ORTIZ FERNANDO ORTIZ - 1 month ago 8
MySQL Question

Total users per period in mysql

I need to see in a judgment quarter registered users

eg:- quarter "Jan-Mar": 20

eg:- quarter "Apr-Jun": 30

id = id_ju1
registration date =date_regis
table = master

SELECT count(DISTINCT(id_ju1)),
CASE
WHEN month(date_regis) >=1 and month(date_regis) <=3 THEN 'Jan-Mar'
WHEN month(date_regis) >=4 and month(date_regis) <=6 THEN 'Apr-Jun' END
FROM master

Answer

You can use QUARTER function for this.

Dates that have a month of Jan-Mar would return 1.
Dates that have a month of Apr-Jun would return 2.
Dates that have a month of Jul-Sep would return 3.
Dates that have a month of Oct-Dec would return 4.

Refer:https://www.techonthenet.com/mysql/functions/quarter.php

SELECT QUARTER(date_regis) AS quarter, COUNT(DISTINCT(id_ju1)) AS idcount
FROM master 
GROUP BY QUARTER(date_regis)
ORDER BY QUARTER(date_regis)