Why is this query not right for SQL Server?

Why is this query not right for SQL Server?

• Daniel R. Tarsky

  Great, thanks guys. It works great.
  

• ALZDBA

  the outer query needs an alias. . . try this:

   

  select ProductID,  sum(TotalCredits), sum(TotalNonCredits) from
    (
    select   ProductID,  sum(ReturnAmount) as TotalCredits, 0 as TotalNonCredits
      from #ReturnItems where CreditCode > 0 group by ProductID
    union
    select ProductID,  0  as TotalCredits, sum(ReturnAmount) as TotalNonCredits
     from #ReturnItems where NonCreditCode > 0 group by ProductID
    ) AnyNameAsQueryAlias
    group by ProductID

  
  
  

• Miguell - MSFT

  You could also remove that piece entirely and uses cases instead:

  select
      ProductID,
      sum(case
          when CreditCode > 0 then ReturnAmount
          else 0
      end) as TotalCredits,
      sum(case
          when NonCreditCode > 0 then ReturnAmount
          else 0
      end) as TotalTotalCredits
  from #ReturnItems
  group by ProductID
  
  

  If the codes are never negative, you could also try:

  select
      ProductID,
      sum(case CreditCode
          when 0 then 0
          else ReturnAmount
      end) as TotalCredits,
      sum(case NonCreditCode
          when 0 then 0
          else ReturnAmount
      end) as TotalTotalCredits
  from #ReturnItems
  group by ProductID
  
  

  
  
• Why is this query not right for SQL Server?