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

Question

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    |

Answer 1

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