you are at Encyclopedia of SQL >> COUNT
COUNT( `column_name` )
This function counts number of rows of column given as input value.
Rows with NULL value may be not counted.
Syntax
SELECT COUNT([DISTINCT] `column_name`)
FROM `table_name`
If we'd like to count only rows with unique value we have to use DISTINCT.
Example 1
Display number of rows in given table
| name | surname | age |
|---|
| Paweł | Kowalski | 3 |
| Piotr | Janik | 7 |
| Paweł | Kowalski | 13 |
query
SELECT COUNT(*)
FROM `people`
result
Example 2 with using DISTINCT
Show name and surname of people who ordered something
| ClientID | name | surname | order_value |
|---|
| 1 | Paweł | Kowalski | 100 |
| 2 | Piotr | Janik | 700 |
| 2 | Paweł | Kowalski | 130 |
query
SELECT `name`,`surname`,COUNT( DISTINCT `ClientID` )
FROM `orders`
COUNT(DISTINCT ) doesn't work with Microsoft Office
result
| nsme | surname |
|---|
| Piotr | Janik |
| Paweł | Kowalski |
Example 3
Display how many orders does each customer filled.
| id | name | surname | order_value |
|---|
| 1 | Jan | Kowalski | 120 |
| 2 | Franciszek | Nowak | 300 |
| 3 | Michał | Nowak | 200 |
| 4 | Jan | Kowalski | 150 |
query
SELECT `name`,`surname`,COUNT(`order_value`)
FROM `orders`
GROUP BY `name`,`surname`
result
| id | name | surname | COUNT(`order_value`) |
|---|
| 1 | Jan | Kowalski | 270 |
| 2 | Franciszek | Nowak | 300 |
| 3 | Michał | Nowak | 200 |
[ wróć na górę strony ]
