Adding two columns SQL 2005

• ritesh.kansara

  SSChasing Mays

  Points: 654

  More actions

  March 17, 2010 at 8:42 am

  #100116

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738171

  More actions

  March 19, 2010 at 8:31 pm

  #1137110

  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

• K Cline

  Ten Centuries

  Points: 1391

  More actions

  March 25, 2010 at 12:54 pm

  #1139947

  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.