mysql - performing addition, subtraction etc on two rows

Question

I have a following Mysql table storing meter readings of different power stations.

Date, station_name, reading
2013-05-06, ABC, 102
2013-05-06, PQR, 122

I want a SQL query with following result for a particular date.

Date, ABC, PQR, ABC-PQR
2013-05-06,102,122,-20

Please show what you tried! – hjpotter92 Jun 09 '13 at 17:01 — hjpotter92, Jun 09 '13 at 17:01
What if there is more than 2 stations? – invisal Jun 09 '13 at 17:01 — invisal, Jun 09 '13 at 17:01

Hart CO · Answer 1 · 2013-06-09T17:13:46.977

1

You could use CASE statements:

SELECT Date
      , SUM(CASE WHEN station_name = 'ABC' THEN reading ELSE 0 END) as ABC
      , SUM(CASE WHEN station_name = 'PQR' THEN reading ELSE 0 END) as PQR
      , SUM(CASE WHEN station_name = 'ABC' THEN reading ELSE 0 END) - SUM(CASE WHEN station_name = 'PQR' THEN reading ELSE 0 END) as 'ABC-PQR'
FROM table
WHERE Date = '20130506'
GROUP BY Date

You can search for MySQL PIVOT to find out other methods people use.

edited Jun 09 '13 at 17:13

answered Jun 09 '13 at 17:01

Hart CO

34,064
6
48
63

I believe he want dynamic column, not separated by comma – invisal Jun 09 '13 at 17:02
@invisal . . . It is entirely unclear. If the OP wants it as a comma separated string, then it is easy enough to use `concat()` or `concat_ws()` to create one. – Gordon Linoff Jun 09 '13 at 17:39

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

I believe that it is not possible to do dynamic column based on value of row. I believe you should do it in application-layer rather than database-layer.

See this post: mysql select dynamic row values as column names, another column as value.

edited May 23 '17 at 12:18

Community

1
1

answered Jun 09 '13 at 16:59

invisal

11,075
4
33
54

mysql - performing addition, subtraction etc on two rows

2 Answers2