Using single SQL query to retrieve counts of two subqueries
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
- 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
- sf_guard_user_profile id and user_id are messed up
- Making a Struts Radio
- Printing out active threads in java
- Redirect Magento category to a URL
- Non-Unicode program displaying Chinese characters as box/square in Windows 7
VN:F [1.9.22_1171]Using single SQL query to retrieve counts of two subqueries,