CODEWITHSUNDEEP
sql
Ashok K Harnal
Ashok K Harnal

Reputation: 1221

SQL Comparing values in two rows

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.

Upvotes: 11

Views: 78157

Answers (3)

user12154835
user12154835

Reputation: 11

<!-- language-all: lang-sql -->
SELECT 
    current_year.category,
    CASE
        WHEN current_year.salesVolume > previous_year.salesVolume THEN 'TRUE'
        ELSE 'FALSE'
    END AS salesIncreasing
FROM 
    sales_data AS current_year
JOIN 
    sales_data AS previous_year
ON 
    current_year.category = previous_year.category
AND 
    current_year.year = previous_year.year + 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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.

Upvotes: 2

David Khuu
David Khuu

Reputation: 967

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.

Upvotes: 20

Related Questions