SUM with condition in query

• texpic

  SSCertifiable

  Points: 5887

  More actions

  August 11, 2012 at 11:55 am

  #264962

  How is this type of query written?

  Id,

  SUM(Cost)

  SUM(Cost) WHERE Code = 0

  COUNT(*)

  COUNT(*) WHERE Code = 0

  (SUM(Cost) WHERE Code = 0) / COUNT(*)

  (COUNT(*) WHERE Code = 0) / COUNT(*)

  CREATE TABLE #Animal

  (Id varchar(10), Code int, Cost money)

  INSERT INTO #Animal

  (Id, Code, Cost)

  SELECT 'A', 0, 10 UNION ALL

  SELECT 'B', 0, 20 UNION ALL

  SELECT 'C', 1, 50 UNION ALL

  SELECT 'D', 1, 60 UNION ALL

  SELECT 'E', 1, 80

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 11, 2012 at 10:33 pm

  #1524813

  You need to use GROUP BY and CASE. But in your example Id is unique, so you not going to get any aggregation.

  Something like this:

  SELECT

  Id

  ,TotalCost = SUM(Cost)

  ,Cost0 = SUM(CASE WHEN Code = 0 THEN Cost END)

  ,TotalCount = COUNT(*)

  ,Count0 = SUM(CASE WHEN Code = 0 THEN 1 END)

  FROM #Animal

  GROUP BY Id

  --Vadim R.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  August 12, 2012 at 3:55 am

  #1524827

  try removing the GROUP BY

  SELECT TotalCost = SUM( Cost ) ,

  Cost0 = SUM( CASE WHEN Code = 0 THEN Cost END ) ,

  TotalCount = COUNT( * ) ,

  Count0 = SUM( CASE WHEN Code = 0 THEN 1 END ),

  Expr1 = SUM( CASE WHEN Code = 0 THEN Cost END ) / COUNT( * ),

  Expr2 = CAST(SUM( CASE WHEN Code = 0 THEN 1 END ) as decimal (10,2))/ CAST(COUNT( * ) as decimal (10,2))

  FROM #Animal;

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• rVadim

  Hall of Fame

  Points: 3969

  More actions

  August 12, 2012 at 11:00 am

  #1524861

  J Livingston SQL (8/12/2012)

  ---

  try removing the GROUP BY

  The OP included Id in the list. So, i thought s/he wants totals by Id, not over all table. So, which is it texpic?

  --Vadim R.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  August 12, 2012 at 11:05 am

  #1524863

  rVadim (8/12/2012)

  ---

  J Livingston SQL (8/12/2012)

  ---

  try removing the GROUP BY

  The OP included Id in the list. So, i thought s/he wants totals by Id, not over all table. So, which is it texpic?

  I agree ...without data and expected results....we dont know.

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• texpic

  SSCertifiable

  Points: 5887

  More actions

  August 12, 2012 at 6:18 pm

  #1524891

  Thanks!