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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy