grouped by age first bike-a1 or bike-part1 course

mail@pastecode.io avatar
unknown
mysql
2 years ago
974 B
3
Indexable
Never
SET @bikePart1ID = 5;
SET @bikeA1ID = 9;

SELECT
	TIMESTAMPDIFF(YEAR, birthday, (
			SELECT
				LEAST((
					SELECT
						IFNULL((
							SELECT
								start_date FROM courses
								JOIN course_students ON course_id = courses.id
							WHERE
								students.id = student_id
								AND course_type_id = @bikePart1ID
							ORDER BY
								start_date ASC
							LIMIT 1), CURRENT_DATE)), (
					SELECT
						IFNULL((
							SELECT
								start_date FROM courses
								JOIN course_students ON course_id = courses.id
							WHERE
								students.id = student_id
								AND course_type_id = @bikeA1ID
							ORDER BY
								start_date ASC
							LIMIT 1), CURRENT_DATE))))) AS age,
	count(*) AS total
FROM
	students
WHERE
	birthday IS NOT NULL
	AND deleted_at IS NULL
	AND EXISTS (
		SELECT
			*
		FROM
			course_students
			JOIN courses ON course_id = courses.id
				AND course_type_id in(@bikePart1ID, @bikeA1ID)
		WHERE
			student_id = students.id)
	GROUP BY
		age