Can you do this in one statement?

Introduction

Ok, here is the problem. Suppose we have a table called SalesRecords in a relational database, like the following:

What we want, is a summary report showing total sales of a selected group of products at each store. For example, if we select to see the summary for Apple, Orange and Pear only, then the report should look like the following:

I will show you how to produce the above result in a single SQL statement. Let's first do this in multiple steps so that you can understand the code easily. First we generate the following result using the SQL statement below. Note that the (case when ... then ... else ... end) expression in the SQL statement generates a new column which is simply a 0-1 indicator, any product that is not among the selected set (Apple, Orange, Pear) will get 0 values in the three generated columns.

select
    Store, 
    (case when Product='Apple' then 1 else 0 end) as IsApple, 
    (case when Product='Orange' then 1 else 0 end) as IsOrange, 
    (case when Product='Pear' then 1 else 0 end) as IsPear,
    Sales
from
    SalesRecords

Let's assume that the above result was saved in a temporary table ReportTempData. Now we can use the following SQL statement to produce the summary report we wanted.

select
    Store,
    sum(IsApple*Sales) as TotalAppleSales,
    sum(IsOrange*Sales) as TotalOrangeSales,
    sum(IsPear*Sales) as TotalPearSales
from 
    ReportTempData
group by Store

Of course we don't really need the temporary table ReportTempData. Here is the combined single SQL statement that does the job:

select
    Store,
    sum(A.IsApple*A.Sales) as TotalAppleSales,
    sum(A.IsOrange*A.Sales) as TotalOrangeSales,
    sum(A.IsPear*A.Sales) as TotalPearSales
from 
    (
        select
            Store, 
            ( case when Product='Apple' then 1 else 0 end) as IsApple, 
            ( case when Product='Orange' then 1 else 0 end) as IsOrange, 
            ( case when Product='Pear' then 1 else 0 end) as IsPear,
            Sales
        from
            SalesRecords
    )  A   
group by Store

We can further simplify it to a statement with only one select query:

select 
 Store, 
  sum(case when Product='Apple' then Sales else 0 end) as TotalAppleSales, 
  sum(case when Product='Orange' then Sales else 0 end) as TotalOrangeSales, 
  sum(case when Product='Pear' then Sales else 0 end) as TotalPearSales 
from  
  SalesRecords 
group by Store

That's it. Thanks for reading and please check out my other articles and tools.

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here