Using Aggregate Functions to exclude CASE Conditionals from Group By

Posted on 2010-01-06
Last Modified: 2012-05-08
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,         
                  WHEN MAX([PF Status]) = 'C' THEN SUM([PF Total])
         END AS [Closed $],
                  WHEN MAX([PF Status]) = 'O' THEN SUM([PF Total])
         END AS [Open $],
                  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'

Question by:selliott80919
    LVL 59

    Accepted Solution

    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 $],
                      WHEN [PF Status] = 'CN' AND [Rebilled Y/N] = 'N' THEN [PF Total]
             END) AS [Cancelled $]

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

    Author Comment


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

    Any other thoughts?

    Thanks again!
    LVL 26

    Expert Comment

    by:Chris Luttrell
    I think Kevin hit your problem on the head, can't improve on that. :)

    Author Comment

    Thanks CGLuttrell, I kept researching and came to the same conclusion.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now