Getting count of records in child table using sele

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

General SQL Server Forums

New to SQL Server Programming

Getting count of records in child table using sele

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2013-05-15 : 04:46:57

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
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2013-05-15 : 05:01:16

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-15 : 05:03:41

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources