From the course: Advanced SQL: High Performance Relational Divisions

Unlock the full course today

Join today to access over 22,400 courses taught by industry experts or purchase this course individually.

Solution: Exact division

Solution: Exact division - SQL Tutorial

From the course: Advanced SQL: High Performance Relational Divisions

Start my 1-month free trial

Solution: Exact division

- [Instructor] The exact division using aggregation, works exactly the same way as I did in chapter two. And this time, the bulk of the work has already been done to support the zero divisions. The key is to realize, that the original query grouped by candidate. And for multiple divisor, we changed it to group by candidate and role. And with that, we lost the insight to how many skills each candidate has in total. To work around it, we need a set of all candidates skill counts to be able to compare to the number of each candidates matching skills. This will allow us, to figure out whether the candidate has any extra skills or not. The easiest way to do it, is to add another width clause, let's name it candidates skill count. Just like role skill count, it should group by candidates and count how many skills each one has. Just like the roll skill counts, it must support candidates with no skills. And therefore, a similar…

Contents