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?

Solution 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.

Solution 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

Solution 3

You will have to list every column in the GROUP BY clause
These columns are those in the SELECT * bit.

This would be correct ANSI SQL anyway.

SELECT * itself is bad anyway: it is always better to explicitly list columns