Using Aggregate Functions to exclude CASE Conditionals from Group By

I am building a simple query that looks at Open / Closed / Cancelled billings by SKU out of a SQL Server DB.  I am using a series of CASE statements based on the condiditonal of the order status (open, closed, cancelled) to aggregate (SUM) the Line totals for the SKU.  Even though the conditionals aren't strictly speaking part of the SELECT statement, the SQL Parser throws an error that they aren't contained in a aggregate or the group by.  Adding them to the group causes duplication of records because they aren't always the same on each order.  What I have done is wrapped them in a MAX statement to allow them to be excluded, but that causes an almost 10x increase in the processing time of the query...  Is there any other way to do this?  The SQL Syntax as it stands today is:

SELECT SKU,         
         CASE
                  WHEN MAX([PF Status]) = 'C' THEN SUM([PF Total])
         END AS [Closed $],
         CASE
                  WHEN MAX([PF Status]) = 'O' THEN SUM([PF Total])
         END AS [Open $],
         CASE
                  WHEN MAX([PF Status]) = 'CN' AND MAX([Rebilled Y/N]) = 'N' THEN SUM([PF Total])
         END AS [Cancelled $]

FROM %Table Name%
WHERE [Order Type] = 'N'
GROUP BY SKU

Thanks!!
selliott80919Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
I believe this is what you are looking for.  You will want to have the conditional inside of the SUM() aggregate function.

SELECT SKU,        
         SUM(CASE [PF Status]
                  WHEN 'C' THEN [PF Total]
         END) AS [Closed $],
         SUM(CASE [PF Status]
                  WHEN 'O' THEN [PF Total]
         END) AS [Open $],
         SUM(CASE
                  WHEN [PF Status] = 'CN' AND [Rebilled Y/N] = 'N' THEN [PF Total]
         END) AS [Cancelled $]

FROM %Table Name%
WHERE [Order Type] = 'N'
GROUP BY SKU
;
0
 
selliott80919Author Commented:
mwvisa1,

That also works, but the SQL interpreter parses that the same way, so the performance is identical.

Any other thoughts?

Thanks again!
0
 
Chris LuttrellSenior Database ArchitectCommented:
I think Kevin hit your problem on the head, can't improve on that. :)
0
 
selliott80919Author Commented:
Thanks CGLuttrell, I kept researching and came to the same conclusion.
0
All Courses

From novice to tech pro — start learning today.