Professional Web Applications Themes

Count equals to ..? - MySQL

Hello, I have a table from which a chart should be generated. there is one column that can have a value between 0-5. Now I want to run a query for column xy which counts the amount of zeros, ones, twos,... How can I build the query that it is known before that 6 results will be given back? If I make something like this: SELECT count(*) FROM cbc_survey c group by xy my chart does not know the amount of columns. I tried SELECT count(xy=1),count(xy=2),... FROM cbc_survey c but did not work. Any ideas? Thank u very much! Best ...

  1. #1

    Default Count equals to ..?

    Hello,

    I have a table from which a chart should be generated. there is one column
    that can have a value between 0-5. Now I want to run a query for column xy
    which counts the amount of zeros, ones, twos,...
    How can I build the query that it is known before that 6 results will be
    given back?
    If I make something like this:
    SELECT count(*) FROM cbc_survey c group by xy my chart does not know the
    amount of columns.

    I tried SELECT count(xy=1),count(xy=2),... FROM cbc_survey c but did not
    work.
    Any ideas?

    Thank u very much!

    Best regards,

    Dennis


    Dennis Guest

  2. #2

    Default Re: Count equals to ..?

    In article <eeqcqm$hhu$mch.sbs.de>, Dennis Kuntzemann
    says... 

    SELECT foo, count(*)
    FROM table
    GROUP BY foo;

    where foo is the 0-5 column

    --
    PleegWat
    Remove caps to reply
    PleegWat Guest

  3. #3

    Default Re: Count equals to ..?


    Dennis Kuntzemann wrote:
     

    Normally when charting something like this, I would want to end up with
    my result data in 2 columns with column 1 giving the `value` and column
    2 giving the count, then the chart would have labels to go with the
    data. I would solve this thus:

    SELECT count( * ) -1
    FROM (
    SELECT 0 AS xy
    UNION ALL SELECT 1 AS xy
    UNION ALL SELECT 2 AS xy
    UNION ALL SELECT 3 AS xy
    UNION ALL SELECT 4 AS xy
    UNION ALL SELECT 5 AS xy
    UNION ALL SELECT xy AS xy FROM cvs
    ) AS t1
    GROUP BY xy


    This would give a 2 column result table like:
    0 2
    1 3
    2 4
    3 0
    4 8
    5 9

    Or if you really want these in a row then add GROUP_CONCAT thus.

    SELECT group_concat( cast( c AS char )
    SEPARATOR ' ' )
    FROM (
    SELECT count( * ) -1 AS c
    FROM (
    SELECT 0 AS xy
    UNION ALL SELECT 1 AS xy
    UNION ALL SELECT 2 AS xy
    UNION ALL SELECT 3 AS xy
    UNION ALL SELECT 4 AS xy
    UNION ALL SELECT 5 AS xy
    UNION ALL SELECT xy AS xy FROM cvs
    ) AS t1
    GROUP BY xy
    ) AS t2

    This works on MySQL 5.

    Others may come up with a simpler way.

    Captain Guest

  4. #4

    Default Re: Count equals to ..?


    PleegWat wrote:
     
    >
    > SELECT foo, count(*)
    > FROM table
    > GROUP BY foo;
    >
    > where foo is the 0-5 column
    >
    > --
    > PleegWat
    > Remove caps to reply[/ref]

    But that won't guarantee to give 6 results. If there are no 3s then he
    will get only 5 results.
    His key phrase is:
     [/ref]

    Captain Guest

Similar Threads

  1. do we have something like equals in Java?
    By sujikrishna in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: April 15th, 12:58 PM
  2. Help. I need a Script to Hide field if field equals zero
    By Ken Stockford in forum Adobe Acrobat Windows
    Replies: 6
    Last Post: May 26th, 09:28 PM
  3. Illustrator CS and Network Equals no Saves
    By Nicholas_Opels@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 1
    Last Post: May 24th, 08:36 PM
  4. Replies: 4
    Last Post: August 10th, 10:03 AM

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