Number of Students per College
UptoSkills Arena is tracking student admissions. Given a list of student enrollments, determine how many students are associated with each college.
studentstable:student_id(integer)college_name(varchar(100))
Return the college_name and the count of students for each college. Order the results alphabetically by college_name.
If a college has no students, it should not appear in the result. Ties in student counts are not applicable here as we are just counting.
Handle NULL college_name by excluding them from the count.
Tables
CREATE TABLE students ( student_id integer PRIMARY KEY, college_name varchar(100) );
Example data
INSERT INTO students (student_id, college_name) VALUES (1, 'IIT Bombay'), (2, 'IIT Bombay'), (3, 'IIT Delhi'), (4, 'IIT Madras'), (5, 'IIT Bombay'), (6, 'IIT Delhi'), (7, 'IIT Guwahati'), (8, NULL), (9, 'IIT Madras'), (10, 'IIT Delhi');
Your query is also checked on hidden datasets with different data.
Hints
Similar problems
- Average Order Value per CustomerEasy
- Total Items Sold by ProductEasy
- Total Sales per Product CategoryEasy
- Total Orders per CustomerEasy
- Total Salary Expenditure by DepartmentEasy
- Find Students with Missing Email AddressesEasy
More topics: Hash Map and Hashing problems, SQL and Databases problems
-- Select college name and count of students
SELECT college_name, COUNT(student_id)
FROM students
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.