Professional Web Applications Themes

how can I count enum values - MySQL

I have a table like this: id car state regdate =========================== 1 toyota good 2006-04-01 2 toyota good 2006-04-05 3 ford bad 2006-05-25 ..... the state column is ENUM ('good','bad','very bad') given a car name (lets say toyota), I want a query that returns the number of 'good', 'bad' and 'very bad' cars registered for each month like this: month good bad very bad april 2006 2 0 0 may 2006 1 1 2 ..... How can I do that?...

Sponsored Links
  1. #1

    Default how can I count enum values

    I have a table like this:

    id car state regdate
    ===========================
    1 toyota good 2006-04-01
    2 toyota good 2006-04-05
    3 ford bad 2006-05-25
    .....

    the state column is ENUM ('good','bad','very bad')

    given a car name (lets say toyota), I want a query that returns the
    number of 'good', 'bad' and 'very bad' cars registered for each month
    like this:

    month good bad very bad
    april 2006 2 0 0
    may 2006 1 1 2
    .....

    How can I do that?

    Sponsored Links
    rbaba99@caramail.com Guest

  2. #2

    Default how can I count enum values

    Hows about
    SELECT date_format( regdate, '%M %Y' ) month,
    sum( state = 'good' ) good,
    sum( state = 'bad' ) bad,
    sum( state = 'very bad' ) very_bad
    FROM `cars`
    WHERE car = 'toyota'
    GROUP BY MONTH

    Captain Guest

  3. #3

    Default how can I count enum values

    You might want to add

    ORDER BY regdate

    at the end of that query

    Captain Guest

Similar Threads

  1. wsdl changes enum values with [Flags]
    By Amil in forum ASP.NET Web Services
    Replies: 0
    Last Post: June 23rd, 10:31 PM
  2. Need Asp recordset Group / Count like values
    By gotcha in forum ASP Database
    Replies: 2
    Last Post: July 20th, 05:12 PM
  3. Binding enum values alphabetically to a drop down list
    By Mark Heimonen in forum ASP.NET General
    Replies: 2
    Last Post: August 4th, 11:56 AM
  4. Ranking count values?
    By Aaron Bertrand - MVP in forum ASP
    Replies: 12
    Last Post: July 22nd, 11:43 PM
  5. note 33904 added to function.fdf-enum-values
    By ceo@rack1.php.net in forum PHP Notes
    Replies: 0
    Last Post: July 10th, 04:13 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