Q

Compare last year sales to this year sales

I have a sales table and want a way to compare 2004 sales versus 2005 sales in one SQL query. The table is:

table SALES
(Distributor_ID number,
product_type char,
product_line char,
sales_year date,
quantity_sold number)

Want to show output broken down by distributor, product_type, product_line. Example (Distributor_id 1):

Dist Type Line Sold2004 Sold2005
 1    A    X       0        5
 1    B    Y       2        3
 1    C    Z       3        0

Your sales_year column is a DATE datatype? Are you sure? <grin />

For simplicity, so as not to clutter the SQL with DATEPART functions to extract the year, let's just assume your sales_year column is actually one of the integer datatypes. If it's truly a DATE datatype, the following SQL will need to be tweaked slightly (each database system has totally different date functions, but they all handle integers the same way).

One way to approach this is to use derived tables to summarize the sales by each year. Then you can simply join the two derived tables by year:

select D1.Dist 
     , D1.Type 
     , D1.Line 
     , D1.Sold2004 
     , D2.Sold2005
  from ( select Distributor_ID     as Dist
              , product_type       as Type
              , product_line       as Line
              , sum(quantity_sold) as Sold2004
           from SALES
          where sales_year = 2004
         group
             by Distributor_ID 
              , product_type 
              , product_line
       ) as D1
full outer
  join ( select Distributor_ID     as Dist
              , product_type       as Type
              , product_line       as Line
              , sum(quantity_sold) as Sold2005
           from SALES
          where sales_year = 2005
         group
             by Distributor_ID
              , product_type 
              , product_line
       ) as D2
    on D1.Distributor_ID = D2.Distributor_ID
   and D1.Type           = D2.Type 
   and D1.Line           = D2.Line 

The "gotcha" here is that it must be a FULL OUTER JOIN. You could have discontinued products with sales in 2004 but no sales in 2005. You could have brand new products with no sales in 2004 but sales in 2005.

Guess what's going to happen if you do it this way, though. The day this report goes out the door, your client's going to ask you to compare 2003, 2004, and 2005 side by side, and then your FULL OUTER JOIN suddenly becomes a whole lot messier. You could end up with partial results for 2003 and 2004, separate rows for 2003 and 2005, and so on. You'll be tempted just to slap another aggregate query around it all with SUMs and another GROUP BY.

There is an easier way.

select Distributor_ID     as Dist
     , product_type       as Type
     , product_line       as Line
     , sum(
         case when sales_year = 2003
              then quantity_sold 
              else 0 end
          ) as Sold2003
     , sum(
         case when sales_year = 2004
              then quantity_sold 
              else 0 end
          ) as Sold2004
     , sum(
         case when sales_year = 2005
              then quantity_sold 
              else 0 end
          ) as Sold2005
  from SALES
group
    by Distributor_ID
     , product_type 
     , product_line

So much easier. Some SQL authors will leave out the ELSE 0, which means that in some instances the CASE value will be NULL. But remember, aggregate functions like SUM ignore NULLs, so this is fairly safe to do. However, my preference is to code ELSE 0 anyway.

This was first published in May 2005

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close