Compare last year sales to this year sales

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.