SQL COUNT() Function
The COUNT() function returns the number of rows that matches a specified criteria.
SQL COUNT(column_name) Syntax
The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:
| SELECT COUNT(column_name) FROM table_name |
SQL COUNT(*) Syntax
The COUNT(*) function returns the number of records in a table:
| SELECT COUNT(*) FROM table_name |
SQL COUNT(DISTINCT column_name) Syntax
The COUNT(DISTINCT column_name) function returns the number of distinct
values of the specified column:
| SELECT COUNT(DISTINCT column_name) FROM table_name |
Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.
SQL COUNT(column_name) Example
We have the following "Orders" table:
| O_Id |
OrderDate |
OrderPrice |
Customer |
| 1 |
2008/11/12 |
1000 |
Hansen |
| 2 |
2008/10/23 |
1600 |
Nilsen |
| 3 |
2008/09/02 |
700 |
Hansen |
| 4 |
2008/09/03 |
300 |
Hansen |
| 5 |
2008/08/30 |
2000 |
Jensen |
| 6 |
2008/10/04 |
100 |
Nilsen |
Now we want to count the number of orders from "Customer Nilsen".
We use the following SQL statement:
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Nilsen' |
The result of the SQL statement above will be 2, because the customer Nilsen has made 2 orders in total:
SQL COUNT(*) Example
If we omit the WHERE clause, like this:
| SELECT COUNT(*) AS NumberOfOrders FROM Orders |
The result-set will look like this:
which is the total number of rows in the table.
SQL COUNT(DISTINCT column_name) Example
Now we want to count the number of unique customers in the "Orders" table.
We use the following SQL statement:
| SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders |
The result-set will look like this:
which is the number of unique customers (Hansen, Nilsen, and Jensen) in the "Orders" table.
Need an easy way to get data into XML, or transform XML to another format?
MapForce lets you map XML data to/from any combination of XML, database, flat file, Excel 2007, XBRL, or Web services data.
Then it transforms data instantly or auto-generates royalty-free data integration code for recurrent conversions.
Download a free, fully functional 30-day trial to experience the following features:
- Easy-to-use, graphical data mapping interface
- Instant data transformation
- XSLT 1.0/2.0 and XQuery code generation
- Java, C#, and C++ code generation
- Advanced data processing functions
- Support for all major relational databases including SQL Server, IBM DB2, Oracle, and more
- Visual Studio & Eclipse integration
Download a fully-functional trial today!
|
|
|
| 
