Query Proble (From Pravin)

• Pravin Patel-491467

  Say Hey Kid

  Points: 674

  More actions

  October 17, 2008 at 6:54 am

  #94148

  SELECT SUM(Amount) AS Amount_1 ,Sum(Amount) AS Amount_2

  FROM xyz

  WHERE Column_1 = 2

  AND Column_2 = 4

  AND DBO.My_function(2,'aaa',4)

  In above query result i need

  Sum of Amount_1 for all three where condition.

  (Column_1 = 2 AND Column_2 = 4 AND AND DBO.My_function(2,'aaa',4))

  But for Sum of Amount_2 i First 2 where condition.

  (Column_1 = 2 AND Column_2 = 4)

  Note that my function rerurn multiple row. so i can't use Case in SELECT.

  Is it possible? I don't need to scan table twice. so, i need both sum in one query.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 17, 2008 at 7:05 am

  #886328

  Like this...

  SELECT

  SUM(Amount) AS Amount_1 ,

  SUM(CASE WHEN Column_1 = 2 AND Column_2 = 4 THEN Amount ELSE 0 END) AS Amount_2

  FROM xyz

  WHERE Column_1 = 2

  AND Column_2 = 4

  AND Column_3 = 5

  Cheers

  ChrisM

  ^{“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