CODEWITHSUNDEEP
mysqlif-statementleft-joinsubtraction
cbros2008
cbros2008

Reputation: 353

MySQL - subtracting certain rows of a table and presenting the results alongside other rows

I have been struggling with getting this query right for hours now. I have a huge amount of data and I want to show just the departments with IDs 10,15,18 and 25. From here, I want to subtract the profits for each dept with ID 18 from 15, i.e. 15-18.

I filter the data with the following query:

SELECT * FROM deptTable WHERE ID IN(10,15,18,25) AND date = '2009-01-25'

dept ---------- date ---------------- ID ----------------- profit
UK ---------- 2009-01-25 ---------- 10 ---------------- 2000
Brazil ------- 2009-01-25 ---------- 10 ---------------- 1300
Japan------- 2009-01-25 --------- 10 ---------------- 2500
Spain------- 2009-01-25 ---------- 10 ---------------- 3200
UK ---------- 2009-01-25 ---------- 15 ---------------- 4000
Brazil ------- 2009-01-25 ---------- 15 ---------------- 1700
Japan------- 2009-01-25 ---------- 15 ---------------- 3500
Spain-------- 2009-01-25 ---------- 15 --------------- 1200
UK ---------- 2009-01-25 ---------- 18 ---------------- 2500
Brazil ------- 2009-01-25 ---------- 18 ---------------- 1300
Japan------- 2009-01-25 --------- 18 ---------------- 2120
Spain------- 2009-01-25 ---------- 18 ---------------- 800
UK ---------- 2009-01-25 ---------- 25 ---------------- 3000
Brazil ------- 2009-01-25 ---------- 25 ---------------- 1850
Japan------- 2009-01-25 --------- 25 ---------------- 1580
Spain-------- 2009-01-25 ---------- 25 --------------- 1070

What I basically want is to then subtract each row with ID 18 from rows with ID 15. Taking the UK as an example:

4000 - 2500 = 1500, doing the rest of the dept regions will give the desired result which is:

dept ---------- date ---------------- ID ----------------- profit
UK ---------- 2009-01-25 ---------- 10 ---------------- 2000
Brazil ------- 2009-01-25 ---------- 10 ---------------- 1300
Japan------- 2009-01-25 --------- 10 ----------------- 2500
Spain------- 2009-01-25 ---------- 10 ---------------- 3200
UK ---------- 2009-01-25 ---------- 15-18 ------------ 1500
Brazil ------- 2009-01-25 ---------- 15-18 ------------ 400
Japan------- 2009-01-25 ---------- 15-18 ----------- 1380
Spain-------- 2009-01-25 ---------- 15-18 ----------- 400
UK ---------- 2009-01-25 ---------- 25 ---------------- 3000
Brazil ------- 2009-01-25 ---------- 25 ---------------- 1850
Japan------- 2009-01-25 ---------- 25 ---------------- 1580
Spain-------- 2009-01-25 ---------- 25 --------------- 1070

2 points:
1. The calculated rows ID column doesn't have to read '15-18', I've just typed '15-18' to help explain the issue
2. The italics/bold are the only calculated rows, all other rows remain the same

Surely something like this is possible?

Thanks,

Upvotes: 2

Views: 470

Answers (1)

Riedsio
Riedsio

Reputation: 9926

I think that something like this will work... 

SELECT a.dept, a.date, IF(a.id=15,'15-18',a.id) AS id, IF(b.profit IS NULL,a.profit,a.profit-b.profit) AS profit
FROM deptTable a 
LEFT JOIN deptTable b ON a.ID=15 AND b.ID=18 AND a.dept=b.dept
WHERE a.ID IN(10,15,25) AND a.date = '2009-01-25'

Tested result:

+--------+------------+-------+--------+
| dept   | date       | id    | profit |
+--------+------------+-------+--------+
| UK     | 2009-01-25 | 10    |   2000 |
| Brazil | 2009-01-25 | 10    |   1300 |
| JAPAN  | 2009-01-25 | 10    |   2500 |
| SPAIN  | 2009-01-25 | 10    |   3200 |
| UK     | 2009-01-25 | 15-18 |   1500 |
| Brazil | 2009-01-25 | 15-18 |    400 |
| JAPAN  | 2009-01-25 | 15-18 |   1380 |
| SPAIN  | 2009-01-25 | 15-18 |    400 |
| UK     | 2009-01-25 | 25    |   3000 |
| Brazil | 2009-01-25 | 25    |   1850 |
| JAPAN  | 2009-01-25 | 25    |   1580 |
| SPAIN  | 2009-01-25 | 25    |   1070 |
+--------+------------+-------+--------+

Upvotes: 2

Related Questions