COUNT

• It returns the number of items in a group.
  • COUNT works like the COUNT_BIG function.
  • The only difference between the two functions is their return values.
  • COUNT always returns an int data type value, when COUNT_BIG always returns bigint.
• If the table has no indexes, then a full table scan is used in the execution plan.
• If the table has indexes, then the query processor would use one of them.
• COUNT(*) returns the number of items in a group.
  • This includes NULL values and duplicates.
  • You can replace the * with a constant.
• COUNT(ALL [expression]) evaluates expression for each row in a group and returns the number of non-null values.
  • The “ALL” is actually the default option and it needs not to be specified.
  • The ALL keyword includes all the non-NULL values.
• COUNT(DISTINCT [expression]) evaluates expression for each row in a group and returns the number of unique, nonnull values.

SELECT COUNT(*) -- All Records
FROM [Person].[Person]

SELECT COUNT([Title]) -- All Values
FROM [Person].[Person];

SELECT COUNT(DISTINCT [Title]) -- Distinct Values
FROM [Person].[Person]

Counting the Rows of Big Tables

• COUNT(*) is often used for counting the rows of a table. Having a COUNT operation that executes excellent at a current time, may be a slow operation after some months of operations in a system. The COUNT on a big table could take long and impose blocking. For that reason we must be aware of some other approaches for obtaining the count info. One way could be the use of Indexed Views. Indexed views are usually created by specific purposes and they work fast. However, remember that it is almost same as having a physical table because the view's data is materialized. The gain is usually achieved by materializing a smaller volume of data so that the counts are obtained quickly and accurately.

CREATE VIEW [Production].[Product_RowCount]
WITH SCHEMABINDING
AS
  SELECT COUNT_BIG(*) AS [RowsCount]
  FROM [Production].[Product];
GO

CREATE UNIQUE CLUSTERED INDEX [IX_Production_Product_RowCount]
ON [Production].[Product_RowCount]([RowsCount]);

OVER [Clause

• OVER clause determines the partitioning and ordering of a rowset before the associated window function is applied.
• The OVER clause defines a window or user-specified set of rows within a query result set.
  • A window function then computes a value for each row in the window.
• You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.
• The following script first partitions the records over "PostalCode" column.
  • After partitioning, it sorts them over "SalesYTD" column.

SELECT
	ROW_NUMBER() OVER(PARTITION BY [PostalCode] ORDER BY [SalesYTD] DESC) AS '#',   
	a.PostalCode,
	s.SalesYTD,
	p.LastName
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
    ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
    ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0
-- If you change the order, it changes the order of the final results.
-- ORDER BY SalesYTD ASC

• The following example uses the MIN, MAX, AVG and COUNT functions with the OVER clause to provide aggregated values for each department.

SELECT DISTINCT
	[d].[DepartmentID],
	[Name] AS 'Department Name',
	COUNT([edh].BusinessEntityID) OVER (PARTITION BY [edh].[DepartmentID]) AS EmployeesPerDept,
	MIN([eph].[Rate]) OVER (PARTITION BY [edh].[DepartmentID]) AS [MinSalary],
	MAX([eph].[Rate]) OVER (PARTITION BY [edh].[DepartmentID]) AS [MaxSalary],
	AVG([eph].[Rate]) OVER (PARTITION BY [edh].[DepartmentID]) AS [AvgSalary]
FROM HumanResources.EmployeePayHistory AS [eph]
INNER JOIN HumanResources.EmployeeDepartmentHistory AS [edh]
    ON eph.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Department AS d
	ON d.DepartmentID = edh.DepartmentID
ORDER BY [d].[DepartmentID]

SELECT
	[d].[DepartmentID],
	[Name] AS 'Department Name',
	COUNT([edh].BusinessEntityID) AS EmployeesPerDept,
	MIN([Rate]) AS [MinSalary],
	MAX([Rate]) AS [MaxSalary],
	AVG([Rate]) AS [AvgSalary]
FROM HumanResources.EmployeePayHistory AS [eph]
INNER JOIN HumanResources.EmployeeDepartmentHistory AS [edh]
    ON eph.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Department AS d
	ON d.DepartmentID = edh.DepartmentID
GROUP BY [d].[DepartmentID], [Name]
ORDER BY [d].[DepartmentID]

Producing a "Moving Average" and "Cumulative Total"