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.
Tag: level-expert
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.
Db2: Stored procedures examples in SQL
Simple examples of SQL stored procedures in Db2.
PostgreSQL: Check which privileges a user has on a table
Let’s see how to check what a user can do with a certain PostgreSQL table, or which users have a certain permission on a table.
SQL Server: Raise errors and warnings
Transact-SQL provides facilities to raise errors and warnings, as well as handling them. Here’s an introduction to this subject.
Db2 temporary tables: ON COMMIT and ON ROLLBACK
Let’s see how to preserve, empty or drop temporary tables when a transaction succeeds (COMMIT) or fails (ROLLBACK).
Get a running sum in SQL
Running sums are useful in situations where you want to get a total, but you also want to see how much each entry of a list contributes to the total. So each entry is accompanied by a partial sum, up to that point of the list. While getting a total in SQL is trivial, getting a running sum requires the use of window functions.
PostgreSQL: Deleting or re-ordering ENUM elements with no disruption
PostgreSQL manual says that it is not possible to delete an element from an ENUM type, nor change their order. However, there is a way to do it in a single transaction.
Db2: Working with aliases
Let’s see how to create, modify and use an alias in Db2. An alias can refer to a table, a sequence or a module. Aliases exist in several databases, but we’re focusing on Db2 syntax here.
Return missing combinations of items from different tables
How to find out which combinations of items from different tables never appear in a third table.