Sql count & where problem

Sql count & where problem

Post by bdlovin » Wed, 19 Jun 2002 20:14:02



I have a parent/child table for Facility and Zone
I can get a list of all counts of zone for each facility

select f.facility_id, rcount = (
select count(*)  from zonefacility zf
where zf.facility_id = f.facility_id
and zf.isdeleted = 0 )
from facility f
where f.isdeleted = 0

but why cant I add a where for the count being 0?

select f.facility_id, rcount = (
select count(*)  from zonefacility zf
where zf.facility_id = f.facility_id
and zf.isdeleted = 0 )
from facility f
where f.isdeleted = 0
and rcount = 0

I get an invalid column error.

 
 
 

Sql count & where problem

Post by Anith Se » Wed, 19 Jun 2002 23:11:51


rcount is nothing but a COLUMN HEADER/ALIAS used to
identify the result of the sub-query. You cannot use it in
the WHERE clause. Instead you may have to use the entire
subQuery to check for 0, like,

WHERE  f.isdeleted = 0
AND (select count(*)  from zonefacility zf
where zf.facility_id = f.facility_id
and zf.isdeleted = 0 ) = 0

- Anith

Quote:>-----Original Message-----
>I have a parent/child table for Facility and Zone
>I can get a list of all counts of zone for each facility

>select f.facility_id, rcount = (
>select count(*)  from zonefacility zf
>where zf.facility_id = f.facility_id
>and zf.isdeleted = 0 )
>from facility f
>where f.isdeleted = 0

>but why cant I add a where for the count being 0?

>select f.facility_id, rcount = (
>select count(*)  from zonefacility zf
>where zf.facility_id = f.facility_id
>and zf.isdeleted = 0 )
>from facility f
>where f.isdeleted = 0
>and rcount = 0

>I get an invalid column error.

>.


 
 
 

Sql count & where problem

Post by Simon Su ( » Sat, 22 Jun 2002 15:33:10


Bruce,

Consider using the following statment,

 SELECT * FROM

                (

                                SELECT f.facility_id,

(SELECT COUNT(*)  FROM zonefacility zf

WHERE zf.facility_id = f.facility_id

AND zf.isdeleted = 0 ) AS rcount

FROM facility f

WHERE f.isdeleted = 0

) AS nf

WHERE nf.rcount = 0

Regards,
Simon Su (MS)
-----------------------------
This posting is provided ?AS IS? with no warranties, and confers no rights

--------------------


>Subject: Sql count & where problem
>Date: Tue, 18 Jun 2002 07:14:02 -0400

>I have a parent/child table for Facility and Zone
>I can get a list of all counts of zone for each facility

>select f.facility_id, rcount = (
>select count(*)  from zonefacility zf
>where zf.facility_id = f.facility_id
>and zf.isdeleted = 0 )
>from facility f
>where f.isdeleted = 0

>but why cant I add a where for the count being 0?

>select f.facility_id, rcount = (
>select count(*)  from zonefacility zf
>where zf.facility_id = f.facility_id
>and zf.isdeleted = 0 )
>from facility f
>where f.isdeleted = 0
>and rcount = 0

>I get an invalid column error.

 
 
 

1. SQL Problem with group by & count

Hi,

I have an SQL problem. Maybe some SQL-Guru out there could help me?

I have a table with companyID and productID. It shows the assignment of
companies and products.

table CompanyProduct
(
    cid     int    not null,
    pid     int    not null
)
PK is cid, pid.
FK1 is cid -> PK (company.cid)
FK2 is pid -> PK (product.pid)

I need a list with the information how many companies have 100 assigned
product, how many have 99, etc.

The number of assigned product is a distinct result of this query:

select distinct count (cp.pid)
from companyproduct cp
group by cp.cid

Okay. Result set looks like:

100
99
55
54
...

Now, I want the number of companies with that number of products behind each
of it. Should look like:

100   4
99    3
55    60
54    38
...   ...

Without the distinct I get a result set in which I can see how often each
company appears. How can I group & count that?

How Do I do it without using cursors? I know there must be a way with one SQL
select and subqueries but I can't figure it out.

Please replay per email.

Arne

2. RESULT: comp.databases.filemaker passes 145:14

3. VA-Herndon-90649--C-C++-Client/Server-HP-HP-UX-ORACLE-Shell Prog. Lang-UNIX-Software Devloper

4. Problem with Join & using Count

5. select row counts

6. Sum & Count problems

7. Problem in upgrading from 6.5 to 7.0

8. Count & GroupBy problem

9. VC5.0&ODBC3.0&SQL problem

10. SQL Counting Problem

11. SQL Query with Count and Wheres Problem

12. Problems with parameter counts with SQL 7 and RDO