Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

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
GROUP BY ShipCity
HAVING COUNT(ShipCity) >= 15
ORDER BY COUNT(ShipCity) DESC

Useful Links

  • The SQL Documentation Tool automatically builds technical documentation for Microsoft SQL Server databases, saving you time and money. A trial version is available for download.

  Site Map | Privacy Policy

All content is 1995 - 2012