MySQL Union Query

[Joannatang]

:dizzy: I have a MySQL Union Query and it doesn't work properly. Basically, I want to calculate the age first using DOB and CourseDate [the DateDiff function] and then count how many patients are between, say, 0 to 15 years old grouped by gender. It works completely fine between 0 to 15 years old and 16 to 24 years old, then the problem starts. When it reaches 25 to 64 years, the female gender fields fragmented into 4 rows whereas I just want it to be displayed in one row! (if unclear, see below for printout):blush:

Please if anyone can help me, it'll be greatly appreciated!! Thanks!!

My Code:
SELECT DISTINCT Gender.GenderType AS Gender, COUNT(Patient.PatientID) AS NumPatients, "[Under 16]" AS AGE
FROM (Patient INNER JOIN PatientCourse ON Patient.PatientID = PatientCourse.PatientID) INNER JOIN Gender ON Patient.GenderID = Gender.GenderID
GROUP BY Gender.GenderType,Patient.DOB, PatientCourse.CourseDate
HAVING (((DateDiff("yyyy",[Patient].[DOB],[PatientCourse].[CourseDate])) Between 0 And 15));
UNION ALL SELECT DISTINCT Gender.GenderType AS Gender, Count(Patient.PatientID) AS NumPatients, " [16-24]" AS AGE
FROM (Patient INNER JOIN PatientCourse ON Patient.PatientID = PatientCourse.PatientID) INNER JOIN Gender ON Patient.GenderID = Gender.GenderID
GROUP BY Gender.GenderType,Patient.DOB, PatientCourse.CourseDate
HAVING (((DateDiff("yyyy",[Patient].[DOB],[PatientCourse].[CourseDate])) Between 16 And 24));
UNION ALL SELECT DISTINCT Gender.GenderType AS Gender, Count(Patient.PatientID) AS NumPatients, " [25-64]" AS AGE
FROM (Patient INNER JOIN PatientCourse ON Patient.PatientID = PatientCourse.PatientID) INNER JOIN Gender ON Patient.GenderID = Gender.GenderID
GROUP BY Gender.GenderType,Patient.DOB, PatientCourse.CourseDate
HAVING (((DateDiff("yyyy",[Patient].[DOB],[PatientCourse].[CourseDate])) Between 25 And 64));
UNION ALL SELECT DISTINCT Gender.GenderType AS Gender, Count(Patient.PatientID) AS NumPatients, " [65-74]" AS AGE
FROM (Patient INNER JOIN PatientCourse ON Patient.PatientID = PatientCourse.PatientID) INNER JOIN Gender ON Patient.GenderID = Gender.GenderID
GROUP BY Gender.GenderType,Patient.DOB, PatientCourse.CourseDate
HAVING (((DateDiff("yyyy",[Patient].[DOB],[PatientCourse].[CourseDate])) Between 65 And 74));
UNION ALL SELECT DISTINCT Gender.GenderType AS Gender, Count(Patient.PatientID) AS NumPatients, " [75-84]" AS AGE
FROM (Patient INNER JOIN PatientCourse ON Patient.PatientID = PatientCourse.PatientID) INNER JOIN Gender ON Patient.GenderID = Gender.GenderID
GROUP BY Gender.GenderType,Patient.DOB, PatientCourse.CourseDate
HAVING (((DateDiff("yyyy",[Patient].[DOB],[PatientCourse].[CourseDate])) Between 75 And 84));
UNION ALL SELECT DISTINCT Gender.GenderType AS Gender, Count(Patient.PatientID) AS NumPatients, "[85 or over]" AS AGE
FROM (Patient INNER JOIN PatientCourse ON Patient.PatientID = PatientCourse.PatientID) INNER JOIN Gender ON Patient.GenderID = Gender.GenderID
GROUP BY Gender.GenderType,Patient.DOB, PatientCourse.CourseDate
HAVING (((DateDiff("yyyy",[Patient].[DOB],[PatientCourse].[CourseDate])) Between 85 And 101));

My results:
GenderNumPatientsAGEFEMALE1[Under 16]MALE1[Under 16]FEMALE1[16-24]MALE1[16-24]FEMALE1[25-64]FEMALE2[25-64]FEMALE3[25-64]FEMALE2[65-74]FEMALE1[75-84]MALE1[75-84]FEMALE1[85 or over]FEMALE2[85 or over]FEMALE7[85 or over]MALE1[85 or over]
:wave:

[Joannatang]

It's running ok, I found a way! :wave:

[degsy]

Hi,

What was the solution?

[Joannatang]

From Microsoft Access, I created a Data Access Page and inserted a pivot table and then used the 'Sum' function to add the same rows together! Works wonders! It does the job anyway! ;)

:wave: