Intro to Over

On previous pages we looked at the utilisation of a single aggregation function in a SELECT statement. Suppose we’d like to wrap an aggregation function around another aggregation function like so: SELECT SUM(COUNT(*)). In SQL, this is unfortunately forbidden.

We could instead rely on subqueries (CTEs in particular) to get around this limitation.

However, there are some instances where we need not result to such verbosity. We now introduce the OVER() clause, which makes such expressions possible in a single statement.

Questions

First, let’s assume we need to answer the following questions:

  • Where do our users come from?
  • How do we express the aggregation as a percentage?

Returning the Count

SQL

First, let’s write a query that returns the actual figures.

SELECT
	Country
,	COUNT(*) AS Cnt
FROM music.Users
GROUP BY
	Country
ORDER BY 
	COUNT(*) DESC;

Output

\[\begin{array}{|c|c|} \hline \text{Country} & \text{Cnt} \\ \hline \text{United States} & 26 \\ \text{France} & 9 \\ \text{Russia} & 7 \\ \text{Poland} & 4 \\ \cdots & \cdots \\ \hline \end{array}\]

Computing the Total

To compute the percentage, we first need to get the total number of users in music.Users. We can augment that above query such that the total is the sum of every value in Cnt. That is: SUM(COUNT(*)).

If we include this expression in our query, we will get an error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

That is, we cannot use an aggregate function on another aggregate function. Why? It kind of boils down to a matter of sequencing.

To rephrase the problem, we want to sum all the values of Cnt in the output above. That is, we need to invoke SUM() after COUNT(*) has given us its result set. Therefore, we need to explicitly tell SQL to use SUM() only once the above result set has been computed.

We can do this with OVER(). This allows us to define the intial result set from the first aggregation as a window. So instead of aggregating on an aggregate, we aggregate on the result set via a window which interposes itself between the two aggregation steps.

Now we write: SUM(COUNT(*)) OVER()

In this case the window is the entire result set returned by the above query. This is because have not specified any partitioning. We are summming all values of Cnt.

SQL

SELECT
	Country
,	COUNT(*) AS Cnt
,	SUM(COUNT(*)) OVER() AS Total
FROM	music.Users
GROUP BY
	Country
ORDER BY 
	COUNT(*) DESC;

Output

\[\begin{array}{|c|c|c|} \hline \text{Country} & \text{Cnt} & \text{Total} \\ \hline \text{United States} & 26 & 100 \\ \text{France} & 9 & 100 \\ \text{Russia} & 7 & 100 \\ \text{Poland} & 4 & 100 \\ \cdots & \cdots & \cdots \\ \hline \end{array}\]

Note: Cnt in the output table does not sum to 100 as I’m not displaying every row in the result set.

Expressing the Percentage

Now we can simply write an expression that divides Cnt by Total and multiplies by 100.0 to get the percentage.

SQL

SELECT
	Country
,	COUNT(*) AS Cnt
,	SUM(COUNT(*)) OVER() AS Total
,	COUNT(*)*100.0/SUM(COUNT(*)) OVER() AS Pcnt
FROM	music.Users;
GROUP BY
	Country
ORDER BY
	COUNT(*) DESC;

Note: when multiplying or dividing an integer by another integer, our result will also be an integer. If we expect a non-integer result, we can cast an int to float by rewriting the integer as a decimal. This is known as implicit conversion. To illustrate this, compare these two expressions: SELECT 1/2 and SELECT 1/2.0.

Output

\[\begin{array}{|c|c|c|c|} \hline \text{Country} & \text{Cnt} & \text{Total} & \text{Pcnt }\\ \hline \text{United States} & 26 & 100 & 26.000000000000\\ \text{France} & 9 & 100 & 9.000000000000 \\ \text{Russia} & 7 & 100 & 7.000000000000 \\ \text{Poland} & 4 & 100 & 4.000000000000 \\ \cdots & \cdots & \cdots & \cdots \\ \hline \end{array}\]

Voila! We have computed the percentage distribution of an aggregation in SQL using a single query. We could have used a subquery to return the total, but that would have been verbose.

Verbose Example

WITH cteTotal AS (
	SELECT
		COUNT(*) AS Total
	FROM 	music.Users;
	)
SELECT
	Country
,	COUNT(*)*100.0/(SELECT Total FROM cteTotal)
FROM	music.Users
GROUP BY 
	Country
ORDER BY COUNT(*) DESC;

Updated: