In SQL Server, the COUNT() function returns the number of items found in a group. You can use it to find out how many rows are in a table or result set.
Syntax
The syntax goes like this:
-- Aggregation Function Syntax
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
-- Analytic Function Syntax
COUNT ( [ ALL ] { expression | * } ) OVER ( [ ] )
ALL applies the aggregate function to all values. This is the default value.
DISTINCT specifies that the function returns the number of unique nonnull values.
expression is an expression of any type, except image, ntext, or text. Aggregate functions and subqueries are not supported in the expression.
* specifies that all rows should be counted and returned, including duplicate rows, and rows that contain null values. COUNT(*) takes no parameters and does not support the use of DISTINCT. It also doesn’t require an expression parameter (because it doesn’t use information about any particular column).
OVER ( [ <partition_by_clause> ] divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group.
Example 1 – Basic Usage
Here’s a basic example showing how this function works:
SELECT COUNT(*) AS 'Row Count' FROM Artists;
Result:
+-------------+ | Row Count | |-------------| | 16 | +-------------+
In this case there are 16 rows in the Artists table.
Just to be sure, here they are:
SELECT * FROM Artists;
Result:
+------------+------------------------+--------------+-------------+ | ArtistId | ArtistName | ActiveFrom | CountryId | |------------+------------------------+--------------+-------------| | 1 | Iron Maiden | 1975-12-25 | NULL | | 2 | AC/DC | 1973-01-11 | NULL | | 3 | Allan Holdsworth | 1969-01-01 | NULL | | 4 | Buddy Rich | 1919-01-01 | NULL | | 5 | Devin Townsend | 1993-01-01 | NULL | | 6 | Jim Reeves | 1948-01-01 | NULL | | 7 | Tom Jones | 1963-01-01 | NULL | | 8 | Maroon 5 | 1994-01-01 | NULL | | 9 | The Script | 2001-01-01 | NULL | | 10 | Lit | 1988-06-26 | NULL | | 11 | Black Sabbath | 1968-01-01 | NULL | | 12 | Michael Learns to Rock | 1988-03-15 | NULL | | 13 | Carabao | 1981-01-01 | NULL | | 14 | Karnivool | 1997-01-01 | NULL | | 15 | Birds of Tokyo | 2004-01-01 | NULL | | 16 | Bodyjar | 1990-01-01 | NULL | +------------+------------------------+--------------+-------------+
As expected, 16 rows are returned.
Notice that the CountryId column contains nothing but null values. This will be useful for the next example.
Example 2 – Specify a Column
The previous example used an asterisk (*) to specify all rows. This results in all rows being counted, regardless of whether there are duplicates or if any contain null values.
You can also specify a particular column. When you do this, null values aren’t counted. That is, any rows containing a null value for that column is not counted.
Here’s an example using the CountryId column as mentioned in the previous example:
SELECT COUNT(CountryId) AS 'Row Count' FROM Artists;
Result:
+-------------+ | Row Count | |-------------| | 0 | +-------------+
As we saw in the previous example, all rows for this column are NULL. Therefore, the resulting row count is zero.
Let’s add some values into that column:
UPDATE Artists
SET CountryId = 2
WHERE ArtistName IN (
'AC/DC',
'Karnivool',
'Birds of Tokyo',
'Bodyjar'
);
Now let’s count the rows for that column again:
SELECT COUNT(CountryId) AS 'Row Count' FROM Artists;
Result:
+-------------+ | Row Count | |-------------| | 4 | +-------------+
Example 3 – With DISTINCT
This example uses the DISTINCT clause to return only distinct rows (i.e. non-duplicates).
In the previous example, I updated the table so that the same CountryId was applied to four artists (I used SET CountryId = 2 for all four artists). This resulted in four rows with the same CountryId.
Here’s what happens if I count how many distinct CountryIds are in that table:
SELECT COUNT(DISTINCT CountryId) 'Distinct CountryIds' FROM Artists;
Result:
+-----------------------+ | Distinct CountryIds | |-----------------------| | 1 | +-----------------------+
This is to be expected, because, although there are four rows with a CountryId, it’s still only one distinct CountryId.
Just to be sure, let’s run it alongside its “non distinct” version:
SELECT COUNT(CountryId) 'Non Distinct', COUNT(DISTINCT CountryId) 'Distinct' FROM Artists;
Result:
+----------------+------------+ | Non Distinct | Distinct | |----------------+------------| | 4 | 1 | +----------------+------------+
So the non distinct version shows how many times the CountryId appears in the table, whereas the DISTINCT version counts multiple occurrences as 1.
Let’s add another CountryId to the table:
UPDATE Artists SET CountryId = 1 WHERE ArtistName = 'Carabao';
And now run the query again:
SELECT COUNT(CountryId) 'Non Distinct', COUNT(DISTINCT CountryId) 'Distinct' FROM Artists;
Result:
+----------------+------------+ | Non Distinct | Distinct | |----------------+------------| | 5 | 2 | +----------------+------------+
Example 4 – Use a WHERE Clause
Here’s a quick example using a WHERE clause.
SELECT COUNT(*) AS 'Row Count' FROM Artists WHERE ActiveFrom >= '2000-01-01';
Result:
+-------------+ | Row Count | |-------------| | 2 | +-------------+
Example 5 – With GROUP BY
Here’s an example of grouping the artists in one column, then counting all the albums for each artist in the other column.
Example:
SELECT ArtistName, COUNT(al.AlbumId) 'Number of Albums' FROM Artists ar INNER JOIN Albums al ON al.ArtistId = ar.ArtistId GROUP BY ArtistName ORDER BY 'Number of Albums' DESC;
Result:
+------------------------+--------------------+ | ArtistName | Number of Albums | |------------------------+--------------------| | Iron Maiden | 5 | | Michael Learns to Rock | 3 | | The Script | 3 | | Tom Jones | 3 | | Devin Townsend | 3 | | Allan Holdsworth | 2 | | Buddy Rich | 1 | | AC/DC | 1 | | Jim Reeves | 1 | +------------------------+--------------------+
Example 6 – With the HAVING Clause
We can tweak the previous example to include only those artists who have more than a certain number of albums. We can do this using the HAVING clause.
SELECT ArtistName, COUNT(al.AlbumId) 'Number of Albums' FROM Artists ar INNER JOIN Albums al ON al.ArtistId = ar.ArtistId GROUP BY ArtistName HAVING COUNT(al.AlbumId) > 2 ORDER BY 'Number of Albums' DESC;
Result:
+------------------------+--------------------+ | ArtistName | Number of Albums | |------------------------+--------------------| | Iron Maiden | 5 | | Michael Learns to Rock | 3 | | The Script | 3 | | Tom Jones | 3 | | Devin Townsend | 3 | +------------------------+--------------------+
Example 7 – Partitioning with the OVER Clause
You can use the OVER clause with PARTITION BY to divide the results into partitions.
In this example, I use OVER (PARTITION BY ArtistName) to list out each album that the artist has produced, as well as the total number of albums for that artist.
SELECT ArtistName, AlbumName, COUNT(AlbumId) OVER (PARTITION BY ArtistName) 'Number of Albums from this Artist' FROM Artists ar INNER JOIN Albums al ON al.ArtistId = ar.ArtistId ORDER BY 'Number of Albums from this Artist' DESC;
Result:
+------------------------+--------------------------+-------------------------------------+ | ArtistName | AlbumName | Number of Albums from this Artist | |------------------------+--------------------------+-------------------------------------| | Iron Maiden | Powerslave | 5 | | Iron Maiden | Somewhere in Time | 5 | | Iron Maiden | Piece of Mind | 5 | | Iron Maiden | Killers | 5 | | Iron Maiden | No Prayer for the Dying | 5 | | AC/DC | Powerage | 3 | | AC/DC | Back in Black | 3 | | AC/DC | Rock or Bust | 3 | | Michael Learns to Rock | Blue Night | 3 | | Michael Learns to Rock | Eternity | 3 | | Michael Learns to Rock | Scandinavia | 3 | | Devin Townsend | Ziltoid the Omniscient | 3 | | Devin Townsend | Casualties of Cool | 3 | | Devin Townsend | Epicloud | 3 | | Tom Jones | Long Lost Suitcase | 3 | | Tom Jones | Praise and Blame | 3 | | Tom Jones | Along Came Jones | 3 | | Allan Holdsworth | All Night Wrong | 2 | | Allan Holdsworth | The Sixteen Men of Tain | 2 | | Buddy Rich | Big Swing Face | 1 | | Jim Reeves | Singing Down the Lane | 1 | | The Script | No Sound Without Silence | 1 | +------------------------+--------------------------+-------------------------------------+
Note that this causes the artist and album count to be repeated across multiple rows, but this is to be expected when we also want to list out each album on its own row.
Example 8 – With STRING_AGG()
If you don’t want each artist and album count to be repeated across multiple rows like in the previous example, you can always use the STRING_AGG() function to output the albums as a list. In this case, you wouldn’t need the OVER clause.
Example:
SELECT ArtistName, STRING_AGG(AlbumName, ', ') 'Albums', COUNT(AlbumId) 'Count' FROM Artists ar INNER JOIN Albums al ON al.ArtistId = ar.ArtistId GROUP BY ArtistName ORDER BY 'Count' DESC;
Result:
+------------------------+--------------------------------------------------------------------------------+---------+ | ArtistName | Albums | Count | |------------------------+--------------------------------------------------------------------------------+---------| | Iron Maiden | Powerslave, Somewhere in Time, Piece of Mind, Killers, No Prayer for the Dying | 5 | | AC/DC | Powerage, Back in Black, Rock or Bust | 3 | | Michael Learns to Rock | Blue Night, Eternity, Scandinavia | 3 | | Devin Townsend | Ziltoid the Omniscient, Casualties of Cool, Epicloud | 3 | | Tom Jones | Long Lost Suitcase, Praise and Blame, Along Came Jones | 3 | | Allan Holdsworth | All Night Wrong, The Sixteen Men of Tain | 2 | | Buddy Rich | Big Swing Face | 1 | | Jim Reeves | Singing Down the Lane | 1 | | The Script | No Sound Without Silence | 1 | +------------------------+--------------------------------------------------------------------------------+---------+
LOTS of Rows?
The COUNT() function returns its result as an int data type. If you have so many rows that the result is bigger than what int can handle, try COUNT_BIG() instead.
COUNT_BIG() works the same as COUNT(), except that its results are returned as a bigint data type value.
You might also consider using APPROX_COUNT_DISTINCT() in some cases.
APPROX_COUNT_DISTINCT() returns an approximate value, rather than a precise value. However, it’s designed to be much more responsive than COUNT() and COUNT_BIG(), so it could be useful for times that responsiveness is more important than precision.
It’s designed to return unique, non-null values, so it would only be relevant for times where you would normally be using the DISTINCT clause with COUNT_BIG().
Also note that, at the time of writing APPROX_COUNT_DISTINCT() is in public preview status.