SQL Server/T-SQL Tutorial/Aggregate Functions/Count

A common mistake with DISTINCT is to code the DISTINCT statement before the COUNT statement.

   <source lang="sql">

Because each row counted is already unique, DISTINCT will have no impact. 5> 6> 7> CREATE TABLE CD ( 8> CD_ID integer NOT NULL PRIMARY KEY, 9> CD_Title varchar(40), 10> Composer_ID integer NOT NULL, 11> Classif_ID integer NOT NULL, 12> SalesPrice money, 13> AverageCost money) 14> GO 1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99) 2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99) 3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99) 4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99) 5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99) 6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99) 7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99) 8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99) 9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99) 10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99) 11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99) 12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99) 13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99) 14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99) 15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99) 16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99) 17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99) 18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99) 19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99) 20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99) 21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99) 22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99) 23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99) 24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99) 25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99) 26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99) 27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99) 28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99) 29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99) 30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99) 31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99) 32> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT DISTINCT COUNT(Classif_ID) from CD 4> GO

---

        31

(1 rows affected) 1> 2> drop table CD; 3> GO</source>

Count function with column alias

   <source lang="sql">

5> 6> create table Billings ( 7> BankerID INTEGER, 8> BillingNumber INTEGER, 9> BillingDate datetime, 10> BillingTotal INTEGER, 11> TermsID INTEGER, 12> BillingDueDate datetime , 13> PaymentTotal INTEGER, 14> CreditTotal INTEGER 15> 16> ); 17> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> SELECT COUNT(*) AS NumberOfBillings, 4> SUM(BillingTotal - PaymentTotal - CreditTotal) AS TotalDue 5> FROM Billings 6> WHERE BillingTotal - PaymentTotal - CreditTotal > 0 7> GO NumberOfBillings TotalDue

---

-----------

              0        NULL

(1 rows affected) 1> 2> drop table Billings; 3> GO</source>

Counting unique values.

   <source lang="sql">

7> CREATE TABLE CD ( 8> CD_ID integer NOT NULL PRIMARY KEY, 9> CD_Title varchar(40), 10> Composer_ID integer NOT NULL, 11> Classif_ID integer NOT NULL, 12> SalesPrice money, 13> AverageCost money) 14> GO 1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99) 2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99) 3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99) 4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99) 5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99) 6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99) 7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99) 8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99) 9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99) 10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99) 11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99) 12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99) 13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99) 14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99) 15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99) 16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99) 17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99) 18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99) 19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99) 20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99) 21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99) 22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99) 23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99) 24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99) 25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99) 26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99) 27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99) 28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99) 29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99) 30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99) 31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99) 32> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT COUNT(DISTINCT Classif_ID) from CD 4> GO

---

         4

(1 rows affected) 1> 2> drop table CD; 3> GO</source>

Counting values including NULL values.

   <source lang="sql">

5> CREATE TABLE CD ( 6> CD_ID integer NOT NULL PRIMARY KEY, 7> CD_Title varchar(40), 8> Composer_ID integer NOT NULL, 9> Classif_ID integer NOT NULL, 10> SalesPrice money, 11> AverageCost money) 12> GO 1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99) 2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99) 3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99) 4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99) 5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99) 6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99) 7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99) 8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99) 9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99) 10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99) 11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99) 12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99) 13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99) 14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99) 15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99) 16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99) 17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99) 18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99) 19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99) 20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99) 21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99) 22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99) 23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99) 24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99) 25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99) 26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99) 27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99) 28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99) 29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99) 30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99) 31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99) 32> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT COUNT(*) from CD 4> GO

---

        31

(1 rows affected) 1> 2> drop table CD; 3> GO</source>

COUNT(*) returns the total count of all records (rows) in the column, including NULL values of selected records by the query.

   <source lang="sql">

DISTINCT is not allowed with this function. 5> 6> 7> 8> CREATE TABLE sales( 9> stor_id char(4) NOT NULL, 10> ord_num varchar(20) NOT NULL, 11> ord_date datetime NOT NULL, 12> qty smallint NOT NULL, 13> payterms varchar(12) NOT NULL, 14> title_id varchar(80) 15> ) 16> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> select COUNT(*) from sales; 4> GO

---

         6

(1 rows affected) 1> 2> drop table sales; 3> 4> GO</source>

COUNT returns the total count of records in a column or the count for DISTINCT (unique) values selected by the query.

   <source lang="sql">

All NULL values are ignored in the total count.

9> CREATE TABLE sales( 10> stor_id char(4) NOT NULL, 11> ord_num varchar(20) NOT NULL, 12> ord_date datetime NOT NULL, 13> qty smallint NOT NULL, 14> payterms varchar(12) NOT NULL, 15> title_id varchar(80) 16> ) 17> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> select COUNT(qty) from sales; 4> GO

---

         6

(1 rows affected) 1> 2> drop table sales; 3> 4> GO 1></source>

Count table row in dynamic sql

   <source lang="sql">

3> CREATE TABLE Orders ( 4> OrderID int IDENTITY (1, 1) NOT NULL , 5> CustomerID nchar (5) NULL , 6> EmployeeID int NULL , 7> OrderDate datetime NULL , 8> RequiredDate datetime NULL , 9> ShippedDate datetime NULL , 10> ShipVia int NULL , 11> Freight money NULL DEFAULT (0), 12> ShipName nvarchar (40) NULL , 13> ShipAddress nvarchar (60) NULL , 14> ShipCity nvarchar (15) NULL , 15> ShipRegion nvarchar (15) NULL , 16> ShipPostalCode nvarchar (10) NULL , 17> ShipCountry nvarchar (15) NULL 18> ) 19> GO 1> 2> DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128); 3> SET @schemaname = N"dbo"; 4> SET @tablename = N"Orders"; 5> 6> DECLARE @objectname AS NVARCHAR(517); 7> SET @objectname = QUOTENAME(@schemaname) + N"." + QUOTENAME(@tablename); 8> EXEC(N"SELECT COUNT(*) FROM " + @objectname + N";"); 9> GO

---

         0

1> 2> drop table orders; 3> GO</source>

Using ISNULL to count all values including NULLs.

   <source lang="sql">

6> 7> CREATE TABLE CD ( 8> CD_ID integer NOT NULL PRIMARY KEY, 9> CD_Title varchar(40), 10> Composer_ID integer NOT NULL, 11> Classif_ID integer NOT NULL, 12> SalesPrice money, 13> AverageCost money) 14> GO 1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99) 2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99) 3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99) 4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99) 5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99) 6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99) 7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99) 8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99) 9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99) 10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99) 11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99) 12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99) 13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99) 14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99) 15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99) 16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99) 17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99) 18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99) 19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99) 20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99) 21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99) 22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99) 23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99) 24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99) 25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99) 26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99) 27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99) 28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99) 29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99) 30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99) 31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99) 32> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> SELECT COUNT(ISNULL(MI,"")) from Store 4> GO Msg 208, Level 16, State 1, Server J\SQLEXPRESS, Line 3 Invalid object name "Store". 1> 2> drop table CD; 3> GO</source>