Professional Web Applications Themes

how to count the number of each item per day - MySQL

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 ...

  1. #1

    Default how to count the number of each item per day

    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.

    omeldoid@gmail.com Guest

  2. #2

    Default Re: how to count the number of each item per day

    On Mar 18, 4:08 pm, com wrote: 

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

    strawberry Guest

  3. #3

    Default Re: how to count the number of each item per day

    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!

    omeldoid@gmail.com Guest

Similar Threads

  1. Getting Total Item Count in Paging Datagrid
    By jason smith in forum ASP.NET Data Grid Control
    Replies: 6
    Last Post: October 28th, 07:26 PM
  2. Count Number of Downloads for PDF File
    By schooloffish in forum Macromedia ColdFusion
    Replies: 3
    Last Post: June 18th, 02:44 AM
  3. Show DataGrid Item Count?
    By Allen Davis in forum ASP.NET Data Grid Control
    Replies: 3
    Last Post: May 5th, 02:01 PM
  4. Item number in a collection?
    By news.microsoft.com in forum ASP
    Replies: 6
    Last Post: July 29th, 01:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139