Write a query to find the cities that have more than one student

Description: Show only those cities where two or more students live.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Group by city, then filter the groups with HAVING COUNT(*) > 1.

---

MySQL query for cities with more than one student

SELECT city, COUNT(*) AS total_students
FROM students
GROUP BY city
HAVING COUNT(*) > 1
ORDER BY city;

Output:

How it works

• WHERE cannot use COUNT(), because it runs before the grouping.
• HAVING filters the groups after they are made.

Read GROUP BY and HAVING.

• Previous Exercise
• Next Exercise

---

Tutorials
No Content Found.

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.