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: CREATE TABLE author ( id INT … Continue reading Functional Dependencies in SQL GROUP BY
Tag: GROUP BY
How to Group By “Nothing” in SQL
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: SELECT count(*) FROM film GROUP BY () This will yield: count | ------| 1000 | What's the point, you're asking? Can't we just … Continue reading How to Group By “Nothing” in SQL
A Beginner’s Guide to the True Order of SQL Operations
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 Guide to the True Order of SQL Operations
SQL GROUP BY and Functional Dependencies: A Very Useful Feature
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 BY and Functional Dependencies: A Very Useful Feature
How SQL GROUP BY Should Have Been Designed – Like Neo4j’s Implicit GROUP BY
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 GROUP BY Should Have Been Designed – Like Neo4j’s Implicit GROUP BY
How to Translate SQL GROUP BY and Aggregations to Java 8
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 Translate SQL GROUP BY and Aggregations to Java 8
Do You Really Understand SQL’s GROUP BY and HAVING clauses?
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 Really Understand SQL’s GROUP BY and HAVING clauses?
MySQL Bad Idea #384
MySQL is a database of compromise. Compromise between running a production-ready relational database and being popular with all sorts of hackers - mostly the ones that don't really like SQL. And because they don't really like SQL, they choose MySQL, as MySQL is very forgiving. It is just as forgiving as their favourite language PHP, … Continue reading MySQL Bad Idea #384