Wednesday, August 1, 2012

Right use of GROUP BY in SQL-queries

This article is about the right use of grouping in SQL-queries. I wrote the article, because this subject is very important. More than that it is not too much complicated and suits to the format of short useful articles, that my blog is for.

Job interviews like questions concerning grouping (using GROUP BY in SQL-queries). If you don't use SQL-queries every day or don't know them well, refresh your memory before your interview.

Let's take an example. Figure 1 shows a table Persons, which stores information about persons. The table has four column headers: ID, name, dept_name, salary.

First of all we use GROUP BY with aggregation.

Aggregate functions are functions that take a collection of values as input and return a single value. SQL offers five built-in aggregate functions:
  • Average: avg
  • Minimum: min
  • Maximum: max
  • Total: sum
  • Count: count

Consider the query "Find the average salary of persons." Try to write the query by yourself before you look at the solution. We write query as follows:

SELECT avg(salary) AS avg_salary
FROM Persons;

The result of the query is represented in Figure 2.

Consider the query "Find the number of persons in each department". To execute the query we need to use grouping.

SELECT dept_name, avg(salary) AS avg_salary
FROM Persons
GROUP BY dept_name;

The result of the query is represented in Figure 3.

    There are two main things we should be mindful of them:
  1. We use GROUP BY with aggregation
  2. The only attributes that appear in the select statement without being aggregated are those that are present in the group by clause.

To understand the second item better we can imagine that the group by statement makes grouping as it's represented in Figure 4.

In Figure 4 the groups are separated by red lines. The query must return one row for every group. And so we can't add the name attribute to the select statement. Think of it now! Why?

It doesn't make sense, because there are many different names in one group.



I used this great book to write the article.

For any questions feel free to write comments or contact me by email.


Labels: , ,