SQL Comparing values in two rows

Question

I have the following data of sales for various categories of items:

category       year         salesVolume  
1              2002          45  
1              2003          47  
2              2002          789
2              2003          908
3              2002          333
3              2003          123
41             2002          111
41             2003          90

Now I want to compare sales volume in the year 2002 to the year 2003, category wise, and write results as:

category        salesIncreasing?
1                 TRUE
2                 TRUE
3                 FALSE
41                FALSE

Is it possible to do it in SQL. If so please let me know. Actually I am using Impala SQL. Thanks.

Answer 1

SELECT 
    a.category, 
    CASE WHEN a.salesVolumes < b.salesVolumes THEN 'TRUE' ELSE 'FALSE' END AS salesIncreasing
FROM MyTable a
INNER JOIN MyTable b ON a.category = b.category
WHERE a.year = 2002
AND b.year = 2003

The idea is to have a single table as a result that let you compare and project the sales into a new data. In order to do this, you join the table with itself, and you use two restrictions in the WHERE clause.

Answer 2

You can do this with conditional aggregation as well as using a join:

select fd.product,
       sum(case when year = 2002 then SalesVolume end) as sales_2002,
       sum(case when year = 2003 then SalesVolume end) as sales_2003,
       (case when sum(case when year = 2002 then SalesVolume end) is null
             then 'New2003'
             when sum(case when year = 2003 then SalesVolume end) is null
             then 'No2003'
             when sum(case when year = 2002 then SalesVolume end) > sum(case when year = 2003 then SalesVolume end) 
             then 'Decreasing'
             when sum(case when year = 2002 then SalesVolume end) = sum(case when year = 2003 then SalesVolume end) 
             then 'Equal'
             else 'Increasing'
        end) as Direction
from followingdata fd
where year in (2002, 2003)
group by fd.product;

The advantage of this approach over a join is that it handles all products, even those that do not appear in both years.