case, group by - getting a bit complicated for me!

[edwardod]

I hope the formatting works on this post!!
(question at bottom!)

Thanks in advance,

Edward


Table structure:
mysql> desc Log;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| date | varchar(20) | | | | |
| action | varchar(30) | | | | |
| detail | varchar(150) | | | | |
+--------+--------------+------+-----+---------+-------+


Data:
mysql> select * from Log limit 10;
+------------+-------------------+--------------+
| date | action | detail |
+------------+-------------------+--------------+
| 2004-06-21 | TechError | some detail |
| 2004-06-21 | EditAction | some detail |
| 2004-06-21 | TechError | some detail |
| 2004-06-20 | CancelAction | some detail |
| 2004-06-20 | TechError | some detail |
| 2004-06-20 | EditAction | some detail |
| 2004-06-19 | TechError | some detail |
| 2004-06-19 | TechError | some detail |
| 2004-06-19 | CancelAction | some detail |
| 2004-06-19 | TechError | some detail |
+------------+-------------------+--------------+
10 rows in set (0.00 sec)

mysql> select date, sum(case when action="TechError" then 1 else 0 end)
from Log
where date > '(current_date - 3)'
group by date
order by date asc;
+------------+-----------------------------------------------------+
| date | sum(case when action="TechError" then 1 else 0 end) |
+------------+-----------------------------------------------------+
| 2004-05-19 | 3 |
| 2004-05-20 | 1 |
| 2004-05-21 | 2 |
+------------------------------------------------------------------+

How do I write a query to return:

+------------+-----------+------------+--------------+
| date | TechError | EditAction | CancelAction |
+------------+-----------+------------+--------------+
| 2004-05-19 | 3 | 0 | 1 |
| 2004-05-20 | 1 | 1 | 1 |
| 2004-05-21 | 2 | 1 | 0 |
+------------+-----------+------------+--------------+

???

 

[TonyGroves]

How about:
[tt]
SELECT
date,
SUM(action="TechError"),
SUM(action="EditAction"),
SUM(action="CancelAction")
FROM log WHERE date > CURDATE()-3
GROUP BY date
ORDER BY date
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[edwardod]

Thanks for the reply Tony.

This is a scaled down version of a much larger table. I need to find a way of doing it without explicitly stating each action in a sum.

Any ideas?

 

[TonyGroves]

If you don't specify each possible value, then there is no way you can show each one in a separate column.

You would have to do:
[tt]
SELECT date,action,count(*) `count`
FROM log WHERE date > CURDATE()-3
GROUP BY date,action
ORDER BY date
[/tt]
This would give something like:[tt]
+------------+--------------+-------+
| date | action | count |
+------------+--------------+-------+
| 2004-05-19 | TechError | 3 |
| 2004-05-19 | EditAction | 0 |
| 2004-05-19 | CancelAction | 1 |
| 2004-05-20 | TechError | 1 |
| 2004-05-20 | EditAction | 1 |
| 2004-05-20 | CancelAction | 1 |
| 2004-05-21 | TechError | 2 |
| 2004-05-21 | EditAction | 1 |
| 2004-05-21 | CancelAction | 0 |
+------------+--------------+-------+
[/tt]


-----
ALTER world DROP injustice, ADD peace;

 

[TonyGroves]

Sorry, CURDATE()-3 is wrong!

It should be:
[tt]CURDATE() - INTERVAL 3 DAY[/tt]

-----
ALTER world DROP injustice, ADD peace;