July 11, 2008 at 1:21 pm
I have a query that is partially right. I just can't figure out how to make the count come out right.
I need it to display as:
OwnerOrganizationIDMaleFemale Unknown
136 810
I am getting:
OwnerOrganizationIDMaleFemale
136 1818
Here's the query, please help.
Declare @minage as int
set @minage = 0
Declare @maxage as int
set @maxage = 5
Declare @Male varchar (1)
set @Male = 'M'
Declare @Female varchar (1)
set @Female = 'F'
select
m.OwnerOrganizationID, count (@Male) as Male, count (@Female) as Female
From
Members m
inner join mastermemberindex mmi on m.family_duid = mmi.diouniqueid
where mmi.status = 1 and --m.memberstatus = 'Active' and
(datediff (year, convert (datetime, m.birthdate), getdate())) =@minage and
(datediff (year, convert (datetime, m.birthdate), getdate())) <=@maxage
group by m.OwnerOrganizationid
order by m.OwnerOrganizationID asc
July 12, 2008 at 12:03 am
Hello, ahi va:
You shouldn't put the @Male or @Female parameters in your COUNT statement, the select statement is working counting every row in the Group By clause.
For only one column that you want to COUNT you should try this:
select m.OwnerOrganizationID, count (SEX) as Male
From
Members m
WHERE SEX = @Male
But in the case of having two columns you should try this:
Declare @minage as int
set @minage = 0
Declare @maxage as int
set @maxage = 5
Declare @Male varchar (1)
set @Male = 'M'
Declare @Female varchar (1)
set @Female = 'F'
select
m.OwnerOrganizationID,
sum(case isnull(SEX,'') when @Male then 1 else 0 end) as Male,
sum(case isnull(SEX,'') when @Female then 1 else 0 end) as Female
From
Members m
inner join mastermemberindex mmi on m.family_duid = mmi.diouniqueid
where mmi.status = 1 and --m.memberstatus = 'Active' and
(datediff (year, convert (datetime, m.birthdate), getdate())) =@minage and
(datediff (year, convert (datetime, m.birthdate), getdate())) <=@maxage
group by m.OwnerOrganizationid
order by m.OwnerOrganizationID asc
(SEX is the name of the column, replace it by the real name of the column in your table)
I hope it helps you.
(Sorry about my english ... pero bueno yo hablo español ...)
Saludos,
José Miguel Gutiérrez Hilaccama
Database Developer - MCTS SQL Server 2005
No way Jose? ... bah
July 12, 2008 at 2:16 pm
José Miguel Gutiérrez Hilaccama,
That worked.
Thank you very much.
Newbie n B-more, Maryland
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply