Tutorial :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?


Solution:1

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


Solution:2

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  


Solution:3

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.


Solution: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  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »