SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Getting count of records in child table using sele
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sparrow37
Posting Yak Master

147 Posts

Posted - 05/15/2013 :  04:46:57  Show Profile  Reply with Quote

I have a stored procedure in which i am trying to select all the columns of a table Table 1. There is another table which uses Table1 primary key as foreign key. I want to count number of records in this foreign key table with that select like this:

SELECT *, count(*) VacancyCount
FROM Table1 hc
LEFT JOIN Table2 hv
on hc.CompanyID = hv.CompanyID
WHERE hc.Deleted = 0
group by hc.CompanyID
ORDER BY NameLang1

but it gives error:

Column 'dbo.Table1.NameLang1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Please suggest how to fix this ?

senthil_nagore
Flowing Fount of Yak Knowledge

India
1007 Posts

Posted - 05/15/2013 :  05:01:16  Show Profile  Visit senthil_nagore's Homepage  Send senthil_nagore a Yahoo! Message  Reply with Quote
All the columns in Select clause, must be in group by clause.

Read about Group by clause in BOL ..

Senthil Kumar C
------------------------------------------------------
MCITP - Database Administration SQL SERVER 2008
MCTS - Database Development SQL SERVER 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 05/15/2013 :  05:03:41  Show Profile  Reply with Quote
not fully clear on what exactly you;re looking at
seems like this is what you're after


SELECT hc.CompanyID, count(hv.CompanyID) VacancyCount
FROM Table1 hc
LEFT JOIN Table2 hv
on hc.CompanyID = hv.CompanyID 
WHERE hc.Deleted = 0
group by hc.CompanyID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000