SEO

#1 (**permalink**) 02-28-2008, 08:12 AM

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

#2 (**permalink**) 02-28-2008, 08:12 AM

In article <eeqcqm$hhu$1@daniel-new.mch.sbs.de>, Dennis Kuntzemann
says...
> 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

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

where foo is the 0-5 column

--
PleegWat
Remove caps to reply

#3 (**permalink**) 02-28-2008, 08:12 AM

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.

#4 (**permalink**) 02-28-2008, 08:12 AM

PleegWat wrote:

> In article <eeqcqm$hhu$1@daniel-new.mch.sbs.de>, Dennis Kuntzemann
> says...
> > 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
>
> SELECT foo, count(*)
> FROM table
> GROUP BY foo;
>
> where foo is the 0-5 column
>
> --
> PleegWat
> Remove caps to reply

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:

> > How can I build the query that it is known before that 6 results will be
> > given back?

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

SEO

Count equals to ..?