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.
Category: Generic SQL
Articles about standard SQL features that will work on the most common DBMSs, and articles that include information about several DBMSs.
Select values that occur multiple times
There are many cases when we want to select values that occur multiple times: customers that ordered the same product multiple times, users with the same email, and so on. We can do this with GROUP BY and HAVING.
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.
Check if more than N rows are returned
Counting the rows that match a WHERE condition can be slow, and may return a very high and precise number that is useful to no one. Let’s see how to just check if a query returns more than N rows.
Comments on database objects
Why and how to write comments on database, tables, and other objects on most DBMSs. How to read comments.
Make searches at the end of a text fast
Searching for a string at the end of a text column is typically slow, because no index is used. How to build a suitable index and make LIKE '%end' fast.
Find texts starting, ending or containing a given string
How to select rows where a text column starts by, ends by, or contains a string. Including slightly less trivial cases.
Aggregate data that match a condition
How to aggregate data that match a given condition. How to perform different aggregations of data that match different conditions, in a single query. Includes convenient SQLite shortcuts.
Find texts that contain HTML tags
How to select text values that contain HTML tags. How to disallow inserting texts that contain HTML tags.
Get distinct values that are stored in multiple columns
Getting a list of unique values from a column is easy. But if the same type of values are written on multiple columns and we want to eliminate duplicates, this requires a little trick.