Dennis Kuntzemann wrote:

> 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

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.