MySQL - Select count of instances of subtype, within a time frame

Question

Need some help with query logic

SQL Fiddle: http://sqlfiddle.com/#!9/3eaf30

Current Query:

select * from (
SELECT year(status_start) as year,MONTH(status_start) AS MONTH,
count( `students`.id) as total
FROM `students` 
INNER JOIN `student_bursaries` ON `students`.`id` = `student_bursaries`.`student_id` 
INNER JOIN `student_bursary_statuses` ON  `student_bursaries`.`id`=`student_bursary_statuses`.`student_bursary_id`
GROUP BY YEAR(student_bursary_statuses.status_start),MONTH(student_bursary_statuses.status_start)
order by year desc, month desc limit 16
) tmp order by year, month

Goal: Trying to get a count of students, per status, per month. Only interested in the latest 16 months and hence the limit. The above query isnt getting me the right figures:

I need 0 where there arent any results
I need a column for each student_bursary_status and not just total
Its currently only looking at the status_start date, and not between the status_start and status_end dates

Note: If a student has status "1" in one month and "2" in the same month, it can count him under both statuses.

Expected Output Example:

|year   |MONTH  |total |1|2|3|4
|2021   |6      |0     |0|0|0|0    
|2021   |7      |0     |0|0|0|0
|2021   |8      |0     |0|0|0|0
|2021   |9      |0     |0|0|0|0
|2021   |10     |0     |0|0|0|0
|2021   |11     |0     |0|0|0|0
|2021   |12     |0     |0|0|0|0
|2022   |1      |2     |2|0|0|0
|2022   |2      |2     |2|0|0|0
|2022   |3      |2     |2|0|0|0
|2022   |4      |2     |2|0|0|0
|2022   |5      |3     |3|0|0|0
|2022   |6      |3     |3|1|0|0
|2022   |7      |3     |2|0|1|0
|2022   |8      |3     |2|0|0|1
|2022   |9      |3     |2|1|1|1

**1/2/3/4 on the right is the different 'status' values ***total only counts a student once, but under the 'status' values, he can be duplicated (ie: if he had a status of '1' in January, and '2' in January, he would be in 'total' once and '1' once and '2' once'

/Updated Query/ A bit closer to what I want but:

still only taking 'status start' into account, instead of per month between status start and status end
manually having to count each status type, was hoping to return a column per status in the db:

So:

select * from (
SELECT year(status_start) as year,MONTH(status_start) AS MONTH,
count( `students`.id) as total,
  COUNT( IF( status = 1, 1, NULL ) ) as status_1,
  COUNT( IF( status = 2, 1, NULL ) ) as status_2,
  COUNT( IF( status = 3, 1, NULL ) ) as status_3,
  COUNT( IF( status = 4, 1, NULL ) ) as status_4
FROM `students` 
INNER JOIN `student_bursaries` ON `students`.`id` =     `student_bursaries`.`student_id` 
INNER JOIN `student_bursary_statuses` ON  `student_bursaries`.`id`=`student_bursary_statuses`.`student_bursary_id`
GROUP BY YEAR(student_bursary_statuses.status_start),MONTH(student_bursary_statuses.status_start)
order by year desc, month desc limit 16
) tmp order by year, month

Update 3

So almost there, but the day of my calendar table appears to be causing some issues and I need that portion of it ignored since I want it grouped by month...

http://sqlfiddle.com/#!9/3eaf30/21

select 
dateTable.date,
COALESCE(yourQuery.total,0) AS mtotal,
COALESCE(yourQuery.status_1,0) AS s1,
COALESCE(yourQuery.status_2,0) AS s2,
COALESCE(yourQuery.status_3,0) AS s3,
COALESCE(yourQuery.status_4,0) AS s4
FROM
(
  SELECT (CURDATE() - INTERVAL c.number MONTH) AS date
  FROM (SELECT singles + tens + hundreds number FROM 
  ( SELECT 0 singles
  UNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3
  UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6
  UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9
  ) singles JOIN 
  (SELECT 0 tens
  UNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30
  UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60
  UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90
  ) tens  JOIN 
  (SELECT 0 hundreds
  UNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300
  UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600
  UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900
  ) hundreds
  ORDER BY number DESC) c  
  WHERE c.number BETWEEN 0 and 16
) as datetable
LEFT JOIN
(
 SELECT 
  status_start,status_end,
  year(status_start) as year,MONTH(status_start) AS MONTH,
  count(`students`.id) as total,
  COUNT( IF( status = 1, 1, NULL ) ) as status_1,
  COUNT( IF( status = 2, 1, NULL ) ) as status_2,
  COUNT( IF( status = 3, 1, NULL ) ) as status_3,
  COUNT( IF( status = 4, 1, NULL ) ) as status_4
  FROM `students` 
  INNER JOIN `student_bursaries` ON `students`.`id` =     `student_bursaries`.`student_id` 
  INNER JOIN `student_bursary_statuses` ON  `student_bursaries`.`id`=`student_bursary_statuses`.`student_bursary_id`
  GROUP BY YEAR(student_bursary_statuses.status_start),MONTH(student_bursary_statuses.status_start)
) AS yourQuery
ON dateTable.date between yourQuery.status_start and yourQuery.status_end
ORDER BY dateTable.date

MySQL - Select count of instances of subtype, within a time frame

0 Answers