'Conditional aggregate'

• simonjw83

  SSC Enthusiast

  Points: 113

  More actions

  January 30, 2009 at 8:37 am

  #72419

  Guys

  Here's the code

  CREATE TABLE #TestTable

  (

  TestID INT ,

  Region VARCHAR(50),

  xValue INT,

  xValue2 INT,

  )

  INSERT #TestTable

  SELECT 1 , 'Europe' , 10 , 30 UNION

  SELECT 2 , 'Europe' , 0 , 2 UNION

  SELECT 3 , 'Europe' , 15 , 85 UNION

  SELECT 4 , 'Europe' , 100 , 16 UNION

  SELECT 5 , 'Europe' , 0 , 12 UNION

  SELECT 6 , 'Asia' , 80 , 51 UNION

  SELECT 7 , 'Asia' , 30 , 17 UNION

  SELECT 8 , 'Asia' , 0 , 65 UNION

  SELECT 9 , 'Asia' , 20 , 22 UNION

  SELECT 10 , 'Asia' , 1 ,101

  SELECT

  Region,

  SUM(xValue) as 'Total' ,

  SUM(xValue2) as 'AnotherTotal' ,

  COUNT(TestID) as 'Count' -- need to only 'COUNT' rows when the xValue is > 0

  FROM #TestTable

  GROUP BY Region

  DROP TABLE #TestTable

  So far so good however I need the 'Count' of xValue to be the count of only rows where xValue is over 0 so for Asia, the value would be 3

  and for Europe it would be 4 instead of the 5 the simple query above produces.

  I can't use a WHERE clause in the main SELECT because then I wouldn't get the correct SUM(xValue2) figure to exclude the data - this is a slightly simplified version of a query that pulls a few other aggregates out and can't exclude rows just because the xValue happens to be 0

  Any ideas guys - thanks in advance

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  January 30, 2009 at 8:49 am

  #935615

  Have you heard about CASE statements? Here is how to make use of it....

  SELECT

  Region,

  SUM(xValue) as 'Total' ,

  SUM(xValue2) as 'AnotherTotal' ,

  --COUNT(TestID) as 'Count' -- need to only 'COUNT' rows when the xValue is > 0

  COUNT( ( CASE WHEN xValue > 0 THEN TestID ELSE NULL END ) ) as 'Count',

  SUM( ( CASE WHEN xValue > 0 THEN 1 ELSE 0 END ) ) as 'CountUsingSUM'

  FROM #TestTable

  GROUP BY Region

  --Ramesh
  

  ---

• simonjw83

  SSC Enthusiast

  Points: 113

  More actions

  January 30, 2009 at 9:01 am

  #935630

  Of course !! Thanks Ramesh. My excuse is it's late Friday afternoon in London - long day !!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 30, 2009 at 9:03 am

  #935632

  simonjw83 (1/30/2009)

  ---

  Of course !! Thanks Ramesh. My excuse is it's late Friday afternoon in London - long day !!

  Not only is it late afternoon, it's friday and it's almost beertime 😎

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  January 30, 2009 at 9:10 am

  #935638

  Chris Morris (1/30/2009)

  ---

  simonjw83 (1/30/2009)

  ---

  Of course !! Thanks Ramesh. My excuse is it's late Friday afternoon in London - long day !!

  Not only is it late afternoon, it's friday and it's almost beertime 😎

  Not for me guys:crying:, I just hate being on work on weekends:angry:...., enjoy your weekend guys...

  BTW, here it's late night already....:w00t:

  --Ramesh
  

  ---

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 30, 2009 at 9:14 am

  #935643

  Ramesh (1/30/2009)

  ---

  Chris Morris (1/30/2009)

  ---

  simonjw83 (1/30/2009)

  ---

  Of course !! Thanks Ramesh. My excuse is it's late Friday afternoon in London - long day !!

  Not only is it late afternoon, it's friday and it's almost beertime 😎

  Not for me guys:crying:, I just hate being on work on weekends:angry:...., enjoy your weekend guys...

  BTW, here it's late night already....:w00t:

  Sorry to hear that Ramesh. I guess that's an inevitable downside of being a DBA as opposed to developer.

  Here in London, it's a little after 4pm and it's getting dark - nothing to do with The Thread either.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  January 30, 2009 at 9:36 am

  #935662

  Chris Morris (1/30/2009)

  ---

  Ramesh (1/30/2009)

  ---

  Chris Morris (1/30/2009)

  ---

  simonjw83 (1/30/2009)

  ---

  Of course !! Thanks Ramesh. My excuse is it's late Friday afternoon in London - long day !!

  Not only is it late afternoon, it's friday and it's almost beertime 😎

  Not for me guys:crying:, I just hate being on work on weekends:angry:...., enjoy your weekend guys...

  BTW, here it's late night already....:w00t:

  Sorry to hear that Ramesh. I guess that's an inevitable downside of being a DBA as opposed to developer.

  Here in London, it's a little after 4pm and it's getting dark - nothing to do with The Thread either.

  And unfortunately, here its same way around both:hehe:..., though I am actually a developer....

  Not all guys are lucky as you people are....

  --Ramesh
  

  ---

• simonjw83

  SSC Enthusiast

  Points: 113

  More actions

  January 30, 2009 at 10:12 am

  #935695

  An it's bloomin cold in London now, Chris you're right it's definitely time for a beer ! Thanks again guys