开发者

Count the number of different column values on every day

开发者 https://www.devze.com 2023-03-16 18:39 出处:网络
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 w

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消