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
.
精彩评论