Count the number of different column values on every day

I have a table:

`tasks` (`id`, `url_id`, `task`, `date`)

task can get values: 1, 2, 3. date is a unix timestamp. how do get a data saying how many tas开发者_开发问答ks where performed on everyday where url_id = 1. The expected result is:

{`count_task_1`: 30, `count_task_2`: 14, `count_task_3`: 30, `date`='2011-03-12'}, [..]

Something like this will give me the number of all entries on that day for the url_id=1:

SELECT COUNT(`id`) FROM `tasks` WHERE `url_id`=1 GROUP BY DATE(FROM_UNIXTIME(`date`));

The only options I see: multiple queries or sub queries (which are pretty much the same).

Thank you to jink for helping me to solve this question.

SELECT `task`, COUNT(`id`) `count`, DATE(FROM_UNIXTIME(`date`)) `date` FROM `tasks` WHERE `url_id`=3 GROUP BY `task`, DATE(FROM_UNIXTIME(`date`));

You can use this high-performance trick for pivoting on different values:

SELECT
    SUM(task_id = 1) as count_task_1,
    SUM(task_id = 2) as count_task_2,
    SUM(task_id = 3) as count_task_3,
    DATE(FROM_UNIXTIME(`date`)) as date
FROM tasks
WHERE url_id=1
GROUP BY 4;

This works because true in mysql (and many other dbs) is 1 and false is 0, so the SUM() of a condition is the count of it being true.