SQL COUNT Function

COUNT returns the number of records from a group, column, or query.

This function returns an integer data value.

COUNT ignores NULL values.

Example

#

This example returns the total number of products.

SELECT COUNT(Id) AS 'Product Count'
  FROM Product
Result:  1 record
Product Count
78

Syntax

Syntax of the COUNT function.

COUNT(value)

value -- a value, column, or subquery.

More Examples

COUNT with GROUP BY

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the number of products sold by month for the year 2013.
SELECT MONTH(OrderDate) AS 'Month',
       COUNT(Id) AS 'Items Sold'
  FROM [Order]
 WHERE YEAR(OrderDate) = 2013
 GROUP BY MONTH(OrderDate)
Result:  12 records
Month Items Sold
1 33
2 29
3 30
4 31
5 32

COUNT with GROUP BY

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
SUPPLIER
Id
CompanyName
ContactName
City
Country
Phone
Fax
Problem: List the number of products by supplier.
SELECT S.CompanyName, 
       COUNT(P.Id) AS 'Number of Products'
  FROM Product P
  JOIN Supplier S ON S.Id = P.SupplierId
 GROUP BY S.CompanyName
Result:  29 records
CompanyName Number of Products
Aux joyeux ecclésiastiques 2
Bigfoot Breweries 3
Cooperativa de Quesos 'Las Cabras' 2
Escargots Nouveaux 1
Exotic Liquids 3

You may also like



Guides


vsn 3.1