SQL Query Using Count function

[bholm]

I want to filter or reduce my query results further based on only those records that have a count > some number.
How can I use a subquery to do this?

I have a ID column in table A and a table B that has Cat column and ID column that references table A ID column value. There are many records in table B for each ID in table A.

I want to SELECT all table A IDs that have a table B Cat value of 101 AND has a count of table B ID values > 2 (i.e. there is more than two records in table B for the table A ID, one of which is Cat 101.)

Hope this makes sense. Your help would be greatly appreciated.

 

[suj2000]

Hi,

Your question is quite confusing could you explain your query by some sample data in row and column format.
eg
Table A
COL1 COL2
A 1
B 1
B 2
C 1
.
...
Could you rewrite your query with such and eg. giving how your data is and what should be the output.

Thanks.

 

[ija]

I am not sure, if I understood correctly. Your select may look like this:
[tt]select *
from Tab_A
where id in (
select b2.id
from Tab_B b1,
Tab_B b2
where b1.rowid<>b2.rowid
and b1.id=b2.id
and b1.cat = '101'
)[/tt]

The statement ensures automatically, that there are at least two rows in Tab_B.
If you will need to restrict a query to count (in some other case), you can use [tt]having count(*)>2[/tt].

 

[sem]

select b.id from b
where exist(select 1 from b b1 where b.id=b1.id and b1.cat=101)
group by id
having count(*)>2