SQL Group By

SQL Group By

This tutorial covers SQL Group By statement.

As the name suggests ,the “group by” clause performs grouping operations.

It is often used to summarise row records with the help of aggregate functions.

Let’s use the table “student_info” from the previous tutorials.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

Group By

The “group by” statement groups data.

For instance, retrieve all students and group them by age.

Below is an example.

SELECT COUNT(age), age FROM student_info
GROUP BY age;

No column name	age
0	NULL
1	19
2	21
1	26

The above query selects the count of all ages as well as the “age” column. It also groups all ages, meaning it summarises each individual age group separately.

We can see that NULL age is 0 (NULL is eliminated by aggregate operations), there is 1 student 19 years old, 2 students 21 years old, and 1 student 26 years old.

You can optimise and make the outcome more readable by combining the query with a SQL alias and a SQL order by statement.

SELECT COUNT(age) AS n, age FROM student_info
GROUP BY age
ORDER BY COUNT(age);

n	age
0	NULL
1	19
1	26
2	21

The result now outputs the data in the order of number of records in an age group (ascending). In addition, it renames the default “No column name” to “n” (abbreviation of number).

We can also add a “where” statement. For instance, let’s retrieve all information as stated above, but only for students named “Patrick”.

SELECT COUNT(age) AS n, age FROM student_info
WHERE firstName = 'Patrick'
GROUP BY age
ORDER BY COUNT(age);

n	age
1	21
1	26

---

Next: SQL Having