Join the two tables and count the number of specific fields in SQL

Question

I have 2 tables, "T_COMMON_COUNTRY" and "T_HEI_STUDENTDATA". using the left join, I joined these tables

this is my request

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 get an idea like this

  | Country ID | County | Student ID | | 1 | USA | 12 | | 1 | USA | 5 | | 2 | UK | 11 | | 2 | UK | 2 | 

I want the number of students (Student_ID) per country,

I want to get an idea as below

  | Country ID | County | Students | | 1 | USA | 2 | | 2 | UK | 2 | 

Answer 1

Use the COUNT function to generate country visit counts.

Try the following:

 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]; 

Answer 2

Use the COUNT function to count the number of students GROUP BY Country_ID and country.

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