Latest web development tutorials

SQL COUNT () function

COUNT () function returns the number of rows that match the specified criteria.

SQL COUNT (column_name) Syntax

The number of values ​​COUNT (column_name) function returns the specified column (NULL not included):

SELECT COUNT(column_name) FROM table_name;

SQL COUNT (*) syntax

COUNT (*) function returns the number of records in the table:

SELECT COUNT(*) FROM table_name;

SQL COUNT (DISTINCT column_name) Syntax

COUNT (DISTINCT column_name) function returns the number of different values ​​of the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name;

Note: COUNT (DISTINCT) applies to ORACLE and Microsoft SQL Server, but can not be used in Microsoft Access.

The demo database

In this tutorial, we will use w3big sample database.

The following data is selected from the "access_log" table: 

+-----+---------+-------+------------+
| aid | site_id | count | date       |
+-----+---------+-------+------------+
|   1 |       1 |    45 | 2016-05-10 |
|   2 |       3 |   100 | 2016-05-13 |
|   3 |       1 |   230 | 2016-05-14 |
|   4 |       2 |    10 | 2016-05-14 |
|   5 |       5 |   205 | 2016-05-14 |
|   6 |       4 |    13 | 2016-05-15 |
|   7 |       3 |   220 | 2016-05-15 |
|   8 |       5 |   545 | 2016-05-16 |
|   9 |       3 |   201 | 2016-05-17 |
+-----+---------+-------+------------+

SQL COUNT (column_name) Examples

SQL statement calculates the following "access_log" table "site_id" = 3 Total views:

Examples

SELECT COUNT(count) AS nums FROM access_log
WHERE site_id=3;

SQL COUNT (*) Examples

The following SQL statement to calculate "access_log" table the total number of records:

Examples

SELECT COUNT (*) AS nums FROM access_log;

Execute the above SQL output results are as follows:

SQL COUNT (DISTINCT column_name) Examples

The following SQL statement to calculate the number of records "access_log" table of different site_id:

Examples

SELECT COUNT (DISTINCT site_id) AS nums FROM access_log;

Execute the above SQL output results are as follows: