count in this query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

count in this query

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2011-08-14 : 07:36:54

Hello,

I have query below,
and it works as expected...

SELECT COUNT(*),c.Full_Title,a.MainFormID,AVG(Total) 'Total',c.JobItemID FROM AssessmentSector a LEFT JOIN Candidates c ON a.EmpId=c.ID
WHERE c.JobItemID='25'
Group BY c.Full_Title,a.MainFormID, c.JobItemID

I want to count record from this query whose total is > 70

in this same query i want it
how can i do so,

Regards,

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-08-15 : 03:32:43

First of all, you've got an AVG field there but you've called it 'Total'. I think its generally good practice to give things useful or accurate fieldnames.

To solve your problem, you need the HAVING command, added after your GROUP BY. I've assumed you want to only show results from your count where the 'Total' field is > 70 so I've ammended you code below.

SELECT COUNT(*),c.Full_Title,a.MainFormID,AVG(Total) 'Total',c.JobItemID FROM AssessmentSector a LEFT JOIN Candidates c ON a.EmpId=c.ID
WHERE c.JobItemID='25'
Group BY c.Full_Title,a.MainFormID, c.JobItemID 
HAVING AVG(Total) > 70

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources