T-SQL: Including Total Row Count in Your Dataset

A common way to include aggregate amounts in a query is to use a subquery:

SELECT ProductID
     , Name
     , ProductNumber
     , Color
     , (SELECT COUNT(*) FROM Production.Product WHERE ProductID BETWEEN 970 AND 980) AS RC
FROM Production.Product
WHERE ProductID BETWEEN 970 AND 980

ProductID       Name                            ProductNumber           Color           RC
970             Touring-2000 Blue, 46           BK-T44U-46              Blue            11      
971             Touring-2000 Blue, 50           BK-T44U-50              Blue            11      
972             Touring-2000 Blue, 54           BK-T44U-54              Blue            11      
973             Road-350-W Yellow, 40           BK-R79Y-40              Yellow          11      
974             Road-350-W Yellow, 42           BK-R79Y-42              Yellow          11      
975             Road-350-W Yellow, 44           BK-R79Y-44              Yellow          11      
976             Road-350-W Yellow, 48           BK-R79Y-48              Yellow          11      
977             Road-750 Black, 58              BK-R19B-58              Black           11      
978             Touring-3000 Blue, 44           BK-T18U-44              Blue            11      
979             Touring-3000 Blue, 50           BK-T18U-50              Blue            11      
980             Mountain-400-W Silver, 38       BK-M38S-38              Silver          11      

This query returns the total number of rows in the dataset. However, it's bulky and requires duplication of code.

A neater solution is to use the PARTITION BY clause:

SELECT ProductID
     , Name
     , ProductNumber
     , Color
     , COUNT(*) OVER (PARTITION BY 1) AS RC
FROM Production.Product
WHERE ProductID BETWEEN 970 AND 980

The PARTITION BY clause can also be used to count the number of records falling into a particular category:

SELECT ProductID
     , Name
     , ProductNumber
     , Color
     , COUNT(*) OVER (PARTITION BY Color) AS RC
FROM Production.Product
WHERE ProductID BETWEEN 970 AND 980

ProductID       Name                            ProductNumber           Color           RC
977             Road-750 Black, 58              BK-R19B-58              Black           1       
978             Touring-3000 Blue, 44           BK-T18U-44              Blue            5       
979             Touring-3000 Blue, 50           BK-T18U-50              Blue            5       
970             Touring-2000 Blue, 46           BK-T44U-46              Blue            5       
971             Touring-2000 Blue, 50           BK-T44U-50              Blue            5       
972             Touring-2000 Blue, 54           BK-T44U-54              Blue            5       
980             Mountain-400-W Silver, 38       BK-M38S-38              Silver          1       
973             Road-350-W Yellow, 40           BK-R79Y-40              Yellow          4       
974             Road-350-W Yellow, 42           BK-R79Y-42              Yellow          4       
975             Road-350-W Yellow, 44           BK-R79Y-44              Yellow          4       
976             Road-350-W Yellow, 48           BK-R79Y-48              Yellow          4

This query returns the number of records which contain the same Color value as the current record.