Select count(*) problem
I use informix online on HP-UX and am having problems with a select.
The following query:
select count(*)
from table_x
wherecolumn_a=12345
group by column_b
returns 11 rows with the following output:
(count(*))
204
172
202
203
203
202
242
202
204
201
196
What I want is a sub-query that returns a count of the rows where the count
is greater than 200. So the final output here would be 9.
I am not interested in an individual count of the rows grouped by column_b -
just a final output of the number of column_b grouped rows > 200.
Don't know if that makes sense.
Any help would be greatfully received.
You could put your select INTO TEMP, then do another select from the temp
table where the count is > 200:
select count(*) c
from table_x
where column_a=12345
group by column_b
into temp hold1;
select count(*)
from hold1
where c > 200;
"darkemperor_uk" <johnsimpson46@johnsimpson46.fsnet.co.uk> wrote in message
news:8gb81m$1i6$1@news8.svr.pol.co.uk...
> I use informix online on HP-UX and am having problems with a select.
> The following query:
> select count(*)
> from table_x
> where> column_a=12345
> group by column_b
> returns 11 rows with the following output:
> (count(*))
> 204
> 172
> 202
> 203
> 203
> 202
> 242
> 202
> 204
> 201
> 196
> What I want is a sub-query that returns a count of the rows where the
count
> is greater than 200. So the final output here would be 9.
> I am not interested in an individual count of the rows grouped by
column_b -
> just a final output of the number of column_b grouped rows > 200.
> Don't know if that makes sense.
> Any help would be greatfully received.
>
>
>
>
>
>
darkemperor_uk wrote:
>
> I use informix online on HP-UX and am having problems with a select.
> The following query:
> select count(*)
> from table_x
> where> column_a=12345
> group by column_b
> returns 11 rows with the following output:
> (count(*))
> 204
> 172
> 202
> 203
> 203
> 202
> 242
> 202
> 204
> 201
> 196
> What I want is a sub-query that returns a count of the rows where the count
> is greater than 200. So the final output here would be 9.
> I am not interested in an individual count of the rows grouped by column_b -
> just a final output of the number of column_b grouped rows > 200.
> Don't know if that makes sense.
> Any help would be greatfully received.
First Informix does not permit you to GROUP BY a column that is NOT in the
SELECTed list of columns so the query you reported should return an error
actually! Beyond that it is easily fixed by selecting column_b and adding
a HAVING clause:
SELECT column_b, count(*)
FROM table_x
WHERE
column_a = 12345
GROUP BY column_b
HAVING count(*) > 200
ORDER BY column_b;
Art S. Kagel