Getting count of records in child table using select statement

Question

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?

Answer 1

Please try:

select      *,     (select COUNT(*) from Table2 hv where hv.CompanyID=hc.CompanyID) VacancyCount from Table1 hc where     hc.Deleted = 0 order by hc.NameLang1, VacancyCount desc 

for ordering using the new column

select * from(     select          *,         CONVERT(NVARCHAR(100), (select COUNT(*) from Table2 hv where hv.CompanyID=hc.CompanyID)) VacancyCount     from Table1 hc     where         hc.Deleted = 0 )x Order by CASE WHEN @OrderByParam = 1 THEN NameLang1 ELSE VacancyCount END 

Provided column NameLang1 and VacancyCount are of same datatype.

Answer 2

You're doing grouping wrong. You need to use all the columns from Table 1 in SELECT instead of '*' and in GROUP BY clause as well.

Or you can try a different approach like this:

SELECT * FROM Table1 hc LEFT JOIN (SELECT CompanyID, COUNT(*) cnt FROM Table2 GROUP BY CompanyID) hv on hc.CompanyID = hv.CompanyID   WHERE hc.Deleted = 0 ORDER BY NameLang1