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];
Share:
10,608
kez
Author by

kez

Currently looking for Full Time or Part Time Job that can work Remotely

Updated on June 11, 2022

Comments

  • kez
    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    |