Total Count in Grouped TSQL Query

group-by, sql, sql-server-2008, t-sql

Solution

See Window functions.

SELECT 
    data.EquipmentId,
    AVG(MeasureValue) AS AverageValue,
    COUNT(*) AS BinCount,
    COUNT(*)/ cast (cnt as float) AS BinCountPercentage
FROM
(SELECT *,
      -- Here is total count of records
        count(*) over() cnt
 FROM MultipleTablesWithJoins) data
GROUP BY data.EquipmentId, cnt

EDIT: forgot to actually divide the numbers.

Problem

I have an performance heavy query, that filters out many unwanted records based on data in other tables etc. I am averaging a column, and also returning the count for each average group. This is all working fine. However, I would also like to include the percentage of the TOTAL count. Is there any way of getting this total count without rerunning the whole query, or increasing the performance load significantly? I would also prefer if I didn't need to completely restructure the sub query (e.g. by getting the total count outside of it), but can do if necessary. ``` SELECT data.EquipmentId, AVG(MeasureValue) AS AverageValue, COUNT(data.*) AS BinCount COUNT(data.*)/ ???TotalCount??? AS BinCountPercentage FROM (SELECT * FROM MultipleTablesWithJoins) data GROUP BY data.EquipmentId ```

Original source