Transact SQL Other Articles Software Reviews
SQL Server Help: How to use count functions in Transact-SQL
Counting the number of rows in a table
This can be achieved in SQL Server by using the Count function, as shown below:
SELECT COUNT(*) FROM sales
When using this query from a programming language, it is normally a good idea to give the resulting column a name, e.g.
SELECT COUNT(*) as 'NumberOfSales' FROM sales
It is also possible to count on a specific column name, although this produces the same result as the previous query, and has no significant performance increase:
SELECT COUNT(stor_id) FROM sales
Counting the distinct number of rows with a WHERE clause
If you need to count the number of distinct rows then using the Distinct function will remove duplicates from the results set. For example, the following will show the distinct cities in the Northwind database's Orders table:
SELECT DISTINCT ShipCity FROM Orders
This type of query can also be used to form the basis of more complex queries, such as the query below which shows the number of shipments made to each distinct city. Only cities with 15 or more shipments are listed, and the ORDER BY clause is used to return the city with the most shipments at the top of the results set.
SELECT DISTINCT(ShipCity), COUNT(ShipCity) AS 'NumberOfShipments' FROM Orders