Group by age in first lesson or course

mail@pastecode.io avatar
unknown
mysql
2 years ago
798 B
3
Indexable
Never
SELECT
		TIMESTAMPDIFF(YEAR, birthday, (
				SELECT
					least(IFNULL((
							SELECT
								`start` FROM lessons
							WHERE
								student_id = students.id
							LIMIT 1), CURRENT_DATE), IFNULL((
						SELECT
							`start_date` FROM courses
							JOIN course_students ON courses.id = course_students.course_id
						WHERE
							student_id = students.id
						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 lessons
				WHERE
					student_id = students.id)
				OR EXISTS (
					SELECT
						* FROM courses
						join course_students on courses.id = course_students.course_id
					WHERE
						student_id = students.id))
	GROUP BY
		age