Join 2 tables and Count the number of occurrence specific field in SQL
10,608
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];
Author by
kez
Currently looking for Full Time or Part Time Job that can work Remotely
Updated on June 11, 2022Comments
-
kez almost 2 years
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 |