Ich habe diese Abfrage:
Code:
select date(c.end_time) as datum,c.state,count(c.state) as checks,s.display_name as service from services as s,hosts as h,servicechecks as c where s.host_object_id=h.host_object_id and c.service_object_id=s.service_object_id and c.state in (0,2)and h.display_name='tsfps01' and s.display_name='Dienste' group by date(c.end_time),c.state;
+------------+-------+--------+---------+
| datum | state | checks | service |
+------------+-------+--------+---------+
| 2007-10-29 | 0 | 43 | Dienste |
| 2007-10-29 | 2 | 75 | Dienste |
| 2007-10-30 | 2 | 285 | Dienste |
| 2007-10-31 | 0 | 142 | Dienste |
| 2007-10-31 | 2 | 140 | Dienste |
| 2007-11-01 | 0 | 288 | Dienste |
| 2007-11-02 | 0 | 283 | Dienste |
| 2007-11-03 | 0 | 288 | Dienste |
| 2007-11-04 | 0 | 288 | Dienste |
| 2007-11-05 | 0 | 197 | Dienste |
| 2007-11-07 | 0 | 56 | Dienste |
+------------+-------+--------+---------+
und ich will mit einer Abfrage die Spalte State in 2 Spalten angezeigt bekommen.
Einmal für State 0 und einmal für State 0+2 oder 2.
Es müsste dann so aussehen:
+------------+-------+--------+---------+
| datum | state 0 | state | service |
+------------+-------+--------+---------+
| 2007-10-29 | 43 | 118 | Dienste |
| 2007-10-30 | 0 | 285 | Dienste |
| 2007-10-31 | 142 | 282 | Dienste |
+--------------------------------------------+