0

I have mysql table

+-------------+-----------------------------------+
| station       | date                | downtime  | 
+-------------+-----------------------------------+
| station a     | 2020-01-05 02:24:00 | 3         |
| station b     | 2020-01-05 02:24:00 | 6         |
| station c     | 2020-01-05 02:24:00 | 0         |
| station a     | 2020-02-05 02:24:00 | 1         |
| station b     | 2020-02-05 02:24:00 | 4         |
| station c     | 2020-02-05 02:24:00 | 7         |
+-------------+-----------------------------------+

and i want to create sql query that would show me each station downtime per month like so

+-------------+----------------------------+
| station       | january      | february  | 
+-------------+----------------------------+
| station a     | 3            | 1         |
| station b     | 6            | 4         |
| station c     | 0            | 7         |

so far I have come up with

SELECT station,month(datum),downtime FROM `downtime` group by station, month(datum)

but this results in each station downtime per month in new rows like this

+-------------+------------------------------------+
| station       | month                | downtime  | 
+-------------+------------------------------------+
| station a     | 1                    | 3         |
| station a     | 2                    | 1         |
| station b     | 1                    | 3         |
| station b     | 2                    | 1         |

not like previous example. How could I make query to display downtime for each station per month while using only one row per station and display downtime per month in columns like in second example ?

Jakadinho
  • 701
  • 1
  • 6
  • 16
  • 1
    This is PIVOT. MySQL do not support it. Emulate, or transform on the client side. – Akina Jan 05 '20 at 06:46
  • Does this answer your question? [MySQL pivot row into dynamic number of columns](https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns) – James Jan 05 '20 at 06:48
  • Alternatively, consider handling issues of data display in application code – Strawberry Jan 05 '20 at 09:37
  • I tought there might be simple solution for this in mysql. At the end I did calculations in client code. – Jakadinho Jan 07 '20 at 08:11

0 Answers0