All courses › Databases, Networks and Security › JOIN and GROUP BY

JOIN and GROUP BY

JOIN combines rows from two tables where the keys match. GROUP BY collects rows with the same value, so that aggregate functions like COUNT, SUM and AVG are computed per group. HAVING filters the groups afterwards.

... JOIN Exam e ON e.student_id = s.id\texttt{... JOIN Exam e ON e.student\_id = s.id}link tables
SELECT course, AVG(grade) ... GROUP BY course\texttt{SELECT course, AVG(grade) ... GROUP BY course}average per course

Symbols

COUNT, SUM, AVG\texttt{COUNT, SUM, AVG}aggregate functions
HAVING\texttt{HAVING}filter on groups

Example

Exams in MAT (4) and PHY (2):

SELECT course, COUNT(*) ... GROUP BY course\texttt{SELECT course, COUNT(*) ... GROUP BY course} gives MAT 4 and PHY 2.

WHERE filters rows before grouping, HAVING filters groups after.
Practise relational databases and SQL for free →

← SELECT and WHERE · IP addresses →

Part of Databases, Networks and Security: Relational databases and SQL.