Quite often we need to aggregate results from many rows. Here are a couple of techniques for doing this in Transact-SQL, most of these queries will probably only work in SQL Server.
In order to have some data to play with I have made up some competition results between the five Nordic countries. The data is held in two tables; Nations (above) and Medals (below). For simplicity’s sake the medal values are stored as CHAR(6) with a CHECK constraint instead of adding another table.
First of we have the standard GROUP BY clause. This trivial example counts the total amount of medals for each nation.
SELECT n.Name AS Nation, COUNT(m.Value) AS Medals FROM Nations n LEFT JOIN Medals m ON m.NationId = n.Id GROUP BY n.Name ORDER BY Medals DESC, Nation
FOR XML is intended to be used to transform result sets into XML. But it can also be abused to concatenate text. For example a comma separated list of medals. Here the STUFF function takes care of the leading comma and the SUBSTRING call is a simple trick to sort the medal values in the natural order (Gold, Silver, Bronze).
SELECT n.Name AS Nation, COALESCE(STUFF(( SELECT ', ' + CAST(COUNT(m.Value) AS VARCHAR(5)) + ' ' + RTRIM(m.Value) FROM Medals m WHERE m.NationId = n.Id GROUP BY m.Value ORDER BY SUBSTRING(m.Value, 3, 2) FOR XML PATH('') ), 1, 2, ''), '') AS Medals FROM Nations n ORDER BY Nation
|Denmark||2 Gold, 2 Silver, 3 Bronze|
|Norway||2 Gold, 3 Bronze|
Sometimes we may want to rotate rows to columns or columns to rows. This is kind of what the PIVOT and UNPIVOT operators in a FROM clause do. Here the result is a pretty useful medal table.
SELECT p.Nation, COALESCE(p.Gold, 0) AS Gold, COALESCE(p.Silver, 0) AS Silver, COALESCE(p.Bronze, 0) AS Bronze FROM (SELECT n.Name AS Nation, m.Value FROM Nations n LEFT JOIN Medals m ON m.NationId = n.Id ) o PIVOT( COUNT(o.Value) FOR o.Value IN (Gold, Silver, Bronze) ) p ORDER BY Gold DESC, Silver DESC, Bronze DESC, Nation
The GROUPING SETS, CUBE and ROLLUP operators are extensions to the GROUP BY clause that allows grouping by more than one set. This example uses it to include a total of all awarded medals per nation together with the totals for each value. The GROUPING function provides a way to distinguish between a NULLs that are the result of missing data and NULLs that are the result of grouping.
SELECT n.Name AS Nation, COUNT(m.Value) AS Quantity, COALESCE(m.Value, 'Total') AS Value FROM Nations n LEFT JOIN Medals m ON m.NationId = n.Id GROUP BY GROUPING SETS ( (n.Name, m.Value), (n.Name) ) HAVING m.Value IS NOT NULL OR GROUPING(m.Value) = 1 ORDER BY Nation, COALESCE(SUBSTRING(m.Value, 3, 2), 'Z')
CROSS APPLY and OUTER APPLY are similar to INNER JOIN and OUTER JOIN but with the difference that the left side is always evaluated first. This allows for some queries that are impossible otherwise or can give performance benefits over alternative queries. Here it is used to get the top 3 medals for each nation, something that may be complicated to do in other ways.
SELECT n.Name AS Nation, t.Value FROM Nations n CROSS APPLY (SELECT TOP 3 m.Value FROM Medals m WHERE m.NationId = n.Id ORDER BY SUBSTRING(m.Value, 3, 2) ) t ORDER BY Nation
The OVER clause is used to apply a function over its own range. This would normally be a partitioned range for aggregative functions or an ordered range for ranking functions. In this case we use it to return the total amount of medals awarded in the competition and then calculate how many percent of the total each nation has earned.
SELECT n.Name AS Nation, CAST(ROUND( 100.0 * COUNT(m.Value) / SUM(COUNT(m.Value)) OVER () , 0) AS INT) AS Percentage FROM Nations n LEFT JOIN Medals m ON m.NationId = n.Id GROUP BY n.Name ORDER BY Percentage DESC, Nation