Count a field two times while comparing to a second table?

[mGis]

Hello All,
I'm using SQL Server7.0 and having trouble using COUNT().

tblAddresses
Add_referenceID  =  USER ID
Add_Region       =  STATE

tblContacts
Cnt_referenceID  =  USER ID
Cnt_Gender       =  'M' or 'F'

What I want to do is get a count of Female Users and a count of Male users per state:

State     Male     Female
AK        46       54
AZ        32       45
FL        20       12
Ect...

Have tried several different ways, but just can't get it,
Thanks in Advance,
Michael

 

[carp]

I don't know how you break out the Male/Female columns in SQL Server (in Oracle, we use DECODE, but that's Oracle!). The following should give you the data you need, just not in the right format:

SELECT a.add_region state, c.cnt_gender gender, count(*) population
FROM tblAddresses A, tblContacts c
WHERE a.Add_referencesID = c.Cnt_referenceID
GROUP BY a.add_region, c.cnt_gender;

This should give you something like:

STATE GENDER POPULATION
AK F 54
AK M 46
AZ F 32
AX M 45

 

[grega]

This works in Sybase (which is almost identical to SQL Server). Give it a go anyway.

select a.Add_Region State, sum(case b.Cnt_Gender when "M" then 1 else 0 end) Male, sum(case b.Cnt_Gender when "F" then 1 else 0 end) Female
from tblAddresses a, tblContacts b
where a.Add_referenceID=b.Cnt_referenceID
group by a.Add_Region

Greg.

 

[mGis]

Thanks Greg,
Works Perfectly!

 

[grega]

Good ... worth bearing in mind that the case statement as used above can prove to be a powerful ally for getting out of tight spots like you had above! Now that *I* understand it I tend to use it a lot.

Greg.

 

[MalcolmW]

Note that Oracle (in recent versions) now supports the use of the (ANSI SQL) CASE statement as an alternative to DECODE. Malcolm