Can you do a count query inside another query and use the results?

Question

Could I run the query:

Select id, (
    select count(*) from tableA as a where a.value < a.id
) 
from tableA as a where id < 5

and get the results I wanted. If not is there a way to do the same thing without having to do 2 querys?

Answer 1

SELECT id, COUNT(id) FROM tableA a WHERE id < 5 GROUP BY id HAVING a.value < a.id

Answer 2

You can but you might want to fix your aliases because it may not work the way you have it.

Also, it will be a lot slower than if you could do it as a single select statement.

Answer 3

try

Select a.id, mycount 
from tableA as a 
join  
(select a1.id, count(*)  as mycount from tableA as a1 where a.value < a.id) b 
   on a.id = b.id
where a.id < 5

Answer 4

Could you give an example of what output you are trying to achieve - and let us know what type of database server (ms, mysql, oracle?). I am thinking something like below, but the exact syntax could be different depending on server type:

SELECT  id, COUNT(CASE WHEN a.value < a.id THEN a.id ELSE NULL END) AS count
FROM    tableA AS a
GROUP BY id