April 4, 2006 at 10:19 pm
I have 2 tables, FAMILY and FAMILY_MEMBER.
The FAMILY table looks like this (I have simplified them to reduce confusion):
fam_id entry_date
1 1/15/2006
2 1/16/2006
3 2/10/2006
The FAMILY_MEMBER table looks like this:
fam_id member_id sex
1 1 M
1 2 F
1 3 F
1 4 M
1 5 M
2 1 M
2 2 F
3 1 M
3 2 F
3 3 F
Basically, the FAMILY table describes a family, and the FAMILY_MEMBER table describes the members of each family. What I would like to do is write a query that will return the number of family members of each sex for each month. So the result should be something like this:
month year total_male total_female
1 2006 4 3
2 2006 1 2
I've tried this, but it doesn't work:
select month(f.service_date) service_month, year(f.service_date) service_year, count(fm_sex_male.fam_id), count(fm_sex_female.fam_id)
from family f
join family_member fm_sex_male on f.fam_id = fm_sex_male.fam_id and fm_sex_male.sex = 'M'
join family_member fm_sex_female on f.fam_id = fm_sex_female.fam_id and fm_sex_female.sex = 'F'
group by year(f.service_date), month(f.service_date)
I feel like I'm missing something obvious, but I'm not seeing what it is. I know I could do this with a bunch of subqueries or udfs computing the counts, but there are a number of other fields in the table besides sex that need to be counted and there will potentially be a large number of records, so I'd like to keep the performance as good as possible. Can anyone help me out? Thanks.
April 5, 2006 at 6:53 am
You could try something like:
SELECT MONTH(f.service_date) AS service_month, YEAR(f.service_date) AS service_date,
SUM(CASE fm.sex WHEN 'M' THEN 1 ELSE 0) AS total_male,
SUM(CASE fm.sex WHEN 'F' THEN 1 ELSE 0) AS total_female
FROM family f
INNER JOIN family_member fm
ON fm.fam_id = f.fam_id
GROUP BY YEAR(f.service_date), MONTH(f.service_date)
Unfortunately, it's tedious for a large number of possible values, and tricky to write for a dynamic list of values. But, for the quoted problem, it should work.
April 5, 2006 at 5:51 pm
I think that will work for me. Thanks a lot.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply