March 17, 2010 at 8:42 am
below is the code I have.
I want to add the male and female and place in new column called TOTAL
- any ideas???
Thanks
it does work- RESULT BELOW:
------------------------Female Male
Any other ethnic groupNULL1
Asian/Asian British - Indian53
Asian/Asian British - Other24
CODE:
drop table #Female
drop table #male
-- drop will delete the table
-- then below it puts the data into a temporary table
SELECT EthnicGroup,COUNT(EthnicGroup) AS 'Female'
Into #Female
FROM [SOLIHULL\kansarar].ALL_QrySMOKING_TEST
WHERE (Quit_Date_Text BETWEEN CONVERT(DATETIME, '2009-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-09-30 00:00:00', 102))
And Gender = 'F'
GROUP BY EthnicGroup
SELECT EthnicGroup,COUNT(EthnicGroup) AS 'Male'
Into #Male
FROM [SOLIHULL\kansarar].ALL_QrySMOKING_TEST
WHERE (Quit_Date_Text BETWEEN CONVERT(DATETIME, '2009-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-09-30 00:00:00', 102))
And Gender = 'M'
GROUP BY EthnicGroup
-- joins the two tables together - calling both fields from different tables
select
IsNull(a.EthnicGroup,b.EthnicGroup) as ethnic_group,
a.female,
b.male
from #female a
full join #male b
on a.EthnicGroup = b.EthnicGroup
March 19, 2010 at 8:31 pm
Do you mean add as in add the counts?
select
IsNull(a.EthnicGroup,b.EthnicGroup) as ethnic_group,
a.female,
b.male,
(a.female + b.male) as total
March 25, 2010 at 12:54 pm
To me, this is a good place to use a pivot. Gets rid of having to take care of those temp tables.
Select Ethnicity, COALESCE(F, 0) As [Female], COALESCE(M, 0) As [Male],
(COALESCE(F, 0) + COALESCE(M, 0)) As [Total]
From
(
Select EthnicGroup, Gender, COUNT(Gender) As [GenderCount]
From [SOLIHULL\kansarar].ALL_QrySMOKING_TEST
Where (Gender In ('F', 'M'))
And (CAST(Quit_Date_Text As Date) Between '7/1/2009' And '9/30/2009')
Group By EthnicGroup, Gender
) As GenderCounts
Pivot
(
MAX(GenderCount) For Gender In ([F], [M])
) As PvtGenderCounts;
If using a version prior to SQL 2008 then CAST the Quit_Date_Text to a SmallDateTime.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply