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,

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 |
+--------+------------+-------+--------+