SEO

#1 (**permalink**) 02-28-2008, 09:25 AM

hello. let's say i have a table with dates and items. on a given date,
many items can be chosen, but only one at a time. i've been trying
hard the whole day to find a solution for counting the number each
item was chosen on a given day.
selecting the total number each item was chosen is easy: select item,
count(item) from table group by item; but i want to do this for each
day.

for example, i expect a result like this:

day - item - count
1 apple 3
1 orange 7
2 apple 1
2 banana 10
2 pear 15

etc.

thanks.

#2 (**permalink**) 02-28-2008, 09:25 AM

On Mar 18, 4:08 pm, omeld...@gmail.com wrote:
> hello. let's say i have a table with dates and items. on a given date,
> many items can be chosen, but only one at a time. i've been trying
> hard the whole day to find a solution for counting the number each
> item was chosen on a given day.
> selecting the total number each item was chosen is easy: select item,
> count(item) from table group by item; but i want to do this for each
> day.
>
> for example, i expect a result like this:
>
> day - item - count
> 1 apple 3
> 1 orange 7
> 2 apple 1
> 2 banana 10
> 2 pear 15
>
> etc.
>
> thanks.

SELECT *,count(*) FROM mytable GROUP BY day

#3 (**permalink**) 02-28-2008, 09:25 AM

ok, i've found the answer. i've compared it against hours, as my test
case doesn't yet have enough days. but here it is, in a simplified
form:

select date_format(date, "%H"), item, count(item)
from mytable
group by item, date_format(date, "%H")

the trick is in using two fields in the `group by' clause!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

how to count the number of each item per day