Join 2 tables and Count the number of occurrence specific field in SQL

count, group-by, select, sql, sql-server

Solution

Use `COUNT` function to generate countrywise student count

Try this:

SELECT C.[COUNTRY_ID], C.[COUNTRY], COUNT(S.[STUDENT_ID]) AS StudentCount
FROM [T_COMMON_COUNTRY] C
LEFT JOIN [T_HEI_STUDENTDATA] S ON C.[COUNTRY] = S.[STDCOUNTRY]
GROUP BY C.[COUNTRY_ID], C.[COUNTRY];

Problem

I have 2 tables , "T_COMMON_COUNTRY" and "T_HEI_STUDENTDATA." using left join I joined these tables this is my query ``` SELECT [T_COMMON_COUNTRY].[COUNTRY_ID], [T_COMMON_COUNTRY].[COUNTRY], [T_HEI_STUDENTDATA].[STUDENT_ID] FROM ([T_COMMON_COUNTRY] LEFT JOIN [T_HEI_STUDENTDATA] ON [T_COMMON_COUNTRY].[COUNTRY] = [T_HEI_STUDENTDATA].[STDCOUNTRY]) ``` now I' getting view like this ``` | Country ID | County | Student ID | | 1 | USA | 12 | | 1 | USA | 5 | | 2 | UK | 11 | | 2 | UK | 2 | ``` I want Count the number of students (Student_IDs) relate to a country , I want get a view exactly like below ``` | Country ID | County | Students | | 1 | USA | 2 | | 2 | UK | 2 | ```

Original source