Getting Count(*) ,SQL Server query for below requirement..
-
Hi all, I have table with data in my database as follows table name :sample (col 'ps' is primary key) village ps val 1 1 56 1 2 67 1 3 65 1 4 70 2 5 74 2 6 64 2 7 32 3 8 46 3 9 56 4 10 64 My requirement is i have to get the Count of 'village' whose val > 60 and val < 60 for that i tried in the following way..(for finding avg 'val' of villages) select sum(val)/count(ps) assessment from sample group by village output: assessment 66 56 59 54 But i need the count of above assessment values > 60 and < 60 i.e count of assessment > 60 , < 60 For that i tried in following way(i know i wrote wrong...but it gives you the idea what i am trying for) select count(*) sample where (select sum(val)/count(ps) assessment from sample group by village) > 60 Please suggest me how to do that... thanks in advance.
-
Hi all, I have table with data in my database as follows table name :sample (col 'ps' is primary key) village ps val 1 1 56 1 2 67 1 3 65 1 4 70 2 5 74 2 6 64 2 7 32 3 8 46 3 9 56 4 10 64 My requirement is i have to get the Count of 'village' whose val > 60 and val < 60 for that i tried in the following way..(for finding avg 'val' of villages) select sum(val)/count(ps) assessment from sample group by village output: assessment 66 56 59 54 But i need the count of above assessment values > 60 and < 60 i.e count of assessment > 60 , < 60 For that i tried in following way(i know i wrote wrong...but it gives you the idea what i am trying for) select count(*) sample where (select sum(val)/count(ps) assessment from sample group by village) > 60 Please suggest me how to do that... thanks in advance.
you need to use HAVING... something like: select sum(val)/count(ps) assessment from sample group by village HAVING sum(val)/count(ps) > 60 let me know if it helps
Intelligence is almost useless for those who have nothing else! Email: caiokf@gmail.com
-
you need to use HAVING... something like: select sum(val)/count(ps) assessment from sample group by village HAVING sum(val)/count(ps) > 60 let me know if it helps
Intelligence is almost useless for those who have nothing else! Email: caiokf@gmail.com
thank you for your suggestion. I got it. Its similar approach as you suggested. select count(*) from sample group by village having avg(value) > 60 select count(*) from sample group by village having avg(value) < 60