subselect in count() ??

subselect in count() ??

Post by Christoph Mangol » Thu, 14 Sep 2000 02:01:38



Hei there,

I just wonder, if it's possible to have a subselect inside the argument of
the count() function.
If I submit he following query (although it doesn't make much sense)

select somecolumn
from sometable
where count (select * from someothertable ) = 17 ;

it always comes back to me with:

db2 (cont.) => db2 (cont.) => SQL0104N An unexpected token
"someothertable" was found following "count (select * from".  Expected
tokens may include:  "<space>".  SQLSTATE=42601 db2 => db2 =>

which doesn't make much sense to me.

Thanks in advance for any help!

Christoph

-----------------
Christoph Mangold

 
 
 

subselect in count() ??

Post by Phil Castl » Thu, 14 Sep 2000 07:03:48


well you can say something like:

Select * from DEPARTMENT where (select count(*) from EMPLOYEE) > 17

this is syntactically correct but a bit meaningless - the condition (select
count(*) from employee) > 17 is the same for all rows in your table, so the
result will either by all rows in DEPARTMENT or  no rows (depending on
whether this condition is true or not).

A more meaningful query would be:

Select * from DEPARTMENT a where (select count(*) from EMPLOYEE b where
b.workdept = a.deptno) >3

which will show you all departments which have more than three employees.

Phil Castle.


.

Quote:

> Hei there,

> I just wonder, if it's possible to have a subselect inside the argument of
> the count() function.
> If I submit he following query (although it doesn't make much sense)

> select somecolumn
> from sometable
> where count (select * from someothertable ) = 17 ;

> it always comes back to me with:

> db2 (cont.) => db2 (cont.) => SQL0104N An unexpected token
> "someothertable" was found following "count (select * from".  Expected
> tokens may include:  "<space>".  SQLSTATE=42601 db2 => db2 =>

> which doesn't make much sense to me.

> Thanks in advance for any help!

> Christoph

> -----------------
> Christoph Mangold


 
 
 

subselect in count() ??

Post by Serge Riela » Fri, 15 Sep 2000 00:55:19


Hi,

You will have to push the scalar subselect into a nested query and tehn
do teh count() ontop.
That should work.
SELECT count(c1) FROM (SELECT (SELECT.... ) AS C1 FROM ..) AS T...

Cheers
Serge

--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support

 
 
 

1. converting a subselect count to a char

Hi,
    I'm doing a select from a parent table that is joined to more than
one child tables.
I'm doing a subselect on the child tables to get a number of rows that
are associated to the parent.
Instead of a number, I'd like to see  Y or N for multiple records and No
records.

Any Suggestions?

Thanks!
Pete

2. Help: two very annoying problems (VFP 5.0)

3. Multiple COUNT statements as subselects

4. Timestamp value and ADO

5. Unique Key Count = 1, but Item Count 43

6. Are ODBC Blobs limited to 32K?

7. order count and orderitem count!

8. ANSI_NULLS Message in SQL2K Stored Procedure

9. Syntax error COUNT() : Using COUNT() with char field

10. SELECT COUNT(*) or SELECT COUNT(id)

11. count(*) or count(1)

12. Fast table count or table block count needed.

13. DTSSource.Count and DTSDestination.Count