WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL COUNT() Function


COUNT() The function returns the number of rows matching the specified criteria.


SQL COUNT (column_name) syntax

COUNT (column_name) function returns the number of values in the specified column (NULL does not count):

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 distinct values for the specified column:

SELECT COUNT (DISTINCT column_name) FROM table_name;

Note: COUNT (DISTINCT) is available for ORACLE and Microsoft SQL Server, but not for Microsoft Access.


Demo database

In this tutorial, we will use the welookups sample database.

Here is the data 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) instance

The following SQL statement calculates the total number of visits with "site_id" = 3 in the "access_log" table:

Example

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


SQL COUNT (*) instance

The following SQL statement counts the total number of records in the "access_log" table:

Example

SELECT COUNT (*) AS nums FROM access_log;

The result of executing the above SQL is as follows:


SQL COUNT (DISTINCT column_name) instance

The following SQL statement counts the number of records at different site_id in the "access_log" table:

Example

SELECT COUNT (DISTINCT site_id) AS nums FROM access_log;

The result of executing the above SQL is as follows: