We’ll show why and how to get what we call “groups of groups”, or aggregations of aggregated data. It means using nested GROUP BY queries.
Category: Generic SQL
Articles about standard SQL features that will work on the most common DBMSs, and articles that include information about several DBMSs.
Non-trivial uses of LIKE in SQL
The LIKE operator is generally well-known. Most SQL users know how to find columns that contain a string, start with a string, or end with a string. But some of them, especially beginners, may not find obvious some other interesting uses of LIKE.
How to get the highest and lowest value in a row in SQL
It is well-known how to get the highest and lowest values in a table column, but it is a bit less known how to get the highest and lowest values in a row. Let’s see how to do it.
How to find the mode in SQL, aka MAX(COUNT(*)) not working
In statistics, the mode is the function that returns the element that appear most often in a series. This is what people try to achieve when they run MAX(COUNT(*))… and they find out that it doesn’t work. Let’s see how to obtain the mode in SQL.
Are RIGHT JOINs ever necessary?
RIGHT JOINs can always be rewritten as LEFT JOINs, and some people consider RIGHT JOINs a bad practice. This article explains why.
Find values above the average
How to find values in a table column that are above (or below) the average of that column.
Find values that appear in multiple columns, in different rows
There are situations when you need to find values that occur in multiple columns, but oitentially in different rows. This can be done with the JOIN or the INTERSECT statement.
Concatenate multiple columns from the same table
Sometimes we want to concatenate multiple table columns, and see them as a single column in our resultset. This may be the case, for example, if we have columns like email1, email2, email3.
Find dates in a given year
Sometimes we want to find all events that happened in a given year. For example, a count of the sales that happened in the year 2000. It’s frequent to run a correct query that turns out to be slow. Let’s see how to do it properly.
The difference between WHERE and HAVING
SQL clauses WHERE and HAVING have different functions. But they both filter out rows, so many people don’t know the important difference between them. Let’s shred some light.