SQL Comparing values in two strings
I have the following sales data for various product categories:
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 2002 to 2003 sales, by category, and record the results as:
category salesIncreasing?
1 TRUE
2 TRUE
3 FALSE
41 FALSE
Is it possible to do this in SQL. If so, please let me know. I am actually using Impala SQL. Thank.
+3
source to share
2 answers
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 that the result is a single table that allows you to compare and project sales into new data. To do this, you join the table with you and you use two constraints in the WHERE clause.
+7
source to share
You can do this with conditional aggregation and also with 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 join
is that it handles all products, even those that are not displayed in both years.
+2
source to share