The SQL standard knows an interesting feature where you can project any functional dependencies of a primary (or unique) key that is listed in the GROUP BY clause without having to add that functional dependency to the GROUP BY clause explicitly. What does this mean? Consider this simple schema: In order to count the number … Continue reading Functional Dependencies…
#group by
7 posts
29 Oct 2021
25 May 2018
The SQL standard knows a lesser known feature called GROUPING SETS. One particular side-effect of that feature is that we can group by “nothing” in SQL. E.g. when querying the Sakila database: This will yield: count | ------| 1000 | What’s the point, you’re asking? Can’t we just omit the GROUP BY clause? Of course, … Continue reading How to…
9 Dec 2016
The SQL language is very intuitive. Until it isn’t. Over the years, a lot of people have criticised the SQL language for a variety of reasons. For instance: IDEs cannot easily guess what auto completion options to offer, because as long as you don’t specify the FROM clause, there are no tables in scope (yet): … Continue reading A Beginner’s…
10 Dec 2015
Relational databases define the term “Functional Dependency” as such (from Wikipedia): In relational database theory, a functional dependency is a constraint between two sets of attributes in a relation from a database. In other words, functional dependency is a constraint that describes the relationship between attributes in a relation. In SQL, functional dependencies appear whenever … Continue reading SQL GROUP…
5 May 2015
In the recent past, we’ve explained the syntactic implications of the SQL GROUP BY clause. If you haven’t already, you should read our article “Do You Really Understand SQL’s GROUP BY and HAVING clauses?“. In essence, adding a GROUP BY clause to your query transforms your query on very implicit levels. The following reminder summarises … Continue reading How SQL…
23 Jan 2015
I couldn’t resist. I have read this question by Hugo Prudente on Stack Overflow. And I knew there had to be a better way than what the JDK has to offer. The question reads: I’m looking for a lambda to refine the data already retrieved. I have a raw resultset, if the user do not … Continue reading How to…
4 Dec 2014
There are some things in SQL that we simply take for granted without thinking about them properly. One of these things are the GROUP BY and the less popular HAVING clauses. Let’s look at a simple example. For this example, we’ll reiterate the example database we’ve seen in this previous article about the awesome LEAD(), … Continue reading Do You…