Recently received a question from someone asking for a single SQL statement to retrieve the counts for two subqueries (or something like that).

I had altered the question and and my answer below.

A company has several customers and courses. Each customer may be signing up on several courses.
Write an SQL query to display the number of women and the number of men signing on each course.

And my simple database design

CREATE TABLE `CUSTOMER` (
`CUSTOMER_ID` INT NULL AUTO_INCREMENT PRIMARY KEY,
`NAME` VARCHAR( 100 ) NULL ,
`GENDER` CHAR( 1 ) NULL ,
`EMAIL` VARCHAR( 150 ) NULL
) ENGINE = MYISAM ;

CREATE TABLE `COURSE` (
`COURSE_ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`NAME` VARCHAR( 50 ) NOT NULL ,
`DESCRIPTION` VARCHAR( 255 ) NOT NULL ,
`START_DATE` DATE NOT NULL ,
`END_DATE` DATE NOT NULL
PRIMARY KEY ( `COURSE_ID` )
) ENGINE = MYISAM ;

CREATE TABLE `CUSTOMER_COURSE` (
`CUSTOMER_ID` INT NULL ,
`COURSE_ID` INT NULL ,
`STATUS` VARCHAR( 5 ) NULL
) ENGINE = MYISAM ;

And the SQL to “display the number of women and the number of men signing on each course”

select
    sum(total_males) as total_males,
    sum(total_females) as total_females,
    COURSE_id
from
    (select COUNT(e.GENDER) as total_males , 0 as total_females, ep.COURSE_ID
        from CUSTOMER e, CUSTOMER_COURSE ep
        where ep.CUSTOMER_ID = e.CUSTOMER_ID and e.GENDER = 'M'
        group by ep.COURSE_ID
    union all
    select 0 as total_males, COUNT(e.GENDER) as total_females ,ep.COURSE_ID
        from CUSTOMER e, CUSTOMER_COURSE ep
        where ep.CUSTOMER_ID = e.CUSTOMER_ID and e.GENDER = 'F'
    group by ep.COURSE_ID
	) t1
group by COURSE_id

Explanation:

• the two subqueries generate 3 columns: total males, total females, course ID
• one of the columns in the two subqueries is 0
• the two subqueries would then UNION ALL to populate all results for the parent SQL
• the parent SQL would then do a SUM/COUNT for the subqueries by grouping the course ID.
• the ‘t1′ is just a empty table name thrown back to the parent for reference