CODEWITHSUNDEEP
sqlpostgresqlsubtraction
Tatsuo
Tatsuo

Reputation: 1

How to Subtract Two Rows which Have One Matching Value in a Column

I'm comparing sales for same week of different year. Like first week of 2010 and 2011. So with my query I get such results.

week|year|sales
1   |2010|5
1   |2011|10
2   |2010|7
2   |2011|13

My query looks like this:

SELECT
min(x.id) AS id,
week as week,
year,
COUNT(*) AS amount,
SUM(price_unit) AS price
FROM (
   SELECT
   so.id as id,
   DATE_PART('week',  so.date_order) AS week,
   DATE_PART('year',  so.date_order) AS year,
 sol.price_unit
 FROM 
sale_order AS so
INNER JOIN sale_order_line AS sol ON sol.order_id = so.id
WHERE so.date_order BETWEEN '2010-01-01' AND '2011-12-31' 
) AS x
GROUP BY
week,
year

What I want to do, is to subtract same weeks of different years, to get the difference in sales. Like week2011-week2010 and results should look like that

week|year     |difference
 1  |2011-2010|5
 2  |2011-2010|6

I just don't have an idea how to subtract like that:)

Upvotes: 0

Views: 1785

Answers (1)

user359040
user359040

Reputation:

To get the difference between the two years, try this:

SELECT
week as week,
'2011-2010' as year,
sum(case calcyear when 2011 then 1 else -1 end) AS amount,
sum(case calcyear when 2011 then price_unit else -1*price_unit end) AS price
FROM (
   SELECT
   so.id as id,
   DATE_PART('week',  so.date_order) AS week,
   DATE_PART('year',  so.date_order) AS calcyear,
 sol.price_unit
 FROM 
sale_order AS so
INNER JOIN sale_order_line AS sol ON sol.order_id = so.id
WHERE so.date_order BETWEEN '2010-01-01' AND '2011-12-31' 
) AS x
GROUP BY
week

Upvotes: 1

Related Questions