Summary of log types per day

Question

tbl_logs has the following columns:

logtimestamp, level, message

Where 'level' can be: error, warning, info, ok

I would like to create a query that returns a summary of log quantities of each level per day:

logdate, error_qty, warning_qty, info_qty, ok_qty

Can this be done in a single query?

I tried:

SELECT DATE(logtimestamp) as logdate, count(*) as qty, level
FROM tbl_logs
GROUP BY logdate, level
ORDER BY logdate DESC

But this query returns one ROW per logdate/level combination (dates will be repeated).

I also attempted to create a query using UNION:

SELECT count(*) as error_qty ... WHERE level = 'error'...
UNION 
SELECT count(*) as warning_qty ... WHERE level = 'warning'...
...

but couldn't make it work.

Can this be done in one single query, or do I need to do several queries and combine the outputs on my application?

Answer 1

SELECT DATE(logtimestamp) AS logdate,
    SUM(level = 'error') AS error_qty,
    SUM(level = 'warning') AS warning_qty,
    SUM(level = 'info') AS info_qty,
    SUM(level = 'ok') AS ok_qty
FROM tbl_logs
GROUP BY logdate
ORDER BY logdate DESC