Counting and grouping

COUNT, SUM, AVG, GROUP BY and HAVING.

SQL

SELECT COUNT(*), MAX(marks), MIN(marks) FROM students;

Output

5 | 91 | 58

GROUP BY

GROUP BY makes one row for each group, so you can calculate per department.

SQL

SELECT dept, COUNT(*), AVG(marks)
FROM students
GROUP BY dept
ORDER BY dept;

Output

CSE | 2 | 87.0
ECE | 1 | 58.0
IT | 2 | 81.0

WHERE filters rows before grouping. HAVING filters groups afterwards. This is a very common interview question.

SQL

SELECT dept, COUNT(*)
FROM students
WHERE marks >= 60
GROUP BY dept
HAVING COUNT(*) > 1
ORDER BY dept;

Output

CSE | 2
IT | 2