Hi here is an example .
The COUNT function returns the number of rows in a query.
The syntax for the COUNT function is:
SELECT COUNT(expression)
FROM tables
WHERE predicates;
Note:
The COUNT function will only count those records in which the field in the brackets is NOT NULL.
For example, if you have the following table called suppliers:
| Supplier_ID |
Supplier_Name |
State |
| 1 |
IBM |
CA |
| 2 |
Microsoft |
|
| 3 |
NVIDIA |
|
The result for this query will return 3.
Select COUNT(Supplier_ID) from suppliers;
While the result for the next query will only return 1, since
there is only one row in the suppliers table where the State field is
NOT NULL.
Select COUNT(State) from suppliers;
Simple Example
For example, you might wish to know how many employees have a salary that is above $25,000 / year.
SELECT COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000;
In this example, we've aliased the count(*) field as "Number
of employees". As a result, "Number of employees" will display as the
field name when the result set is returned.
Example using DISTINCT
You can use the DISTINCT clause within the COUNT function.
For example, the SQL statement below returns the number of
unique departments where at least one employee makes over $25,000 /
year.
SELECT COUNT(DISTINCT department) as "Unique departments"
FROM employees
WHERE salary > 25000;
Again, the count(DISTINCT department) field is aliased as
"Unique departments". This is the field name that will display in the
result set.
Example using GROUP BY
In some cases, you will be required to use a GROUP BY clause with the COUNT function.
For example, you could use the COUNT function to return the
name of the department and the number of employees (in the associated
department) that make over $25,000 / year.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
Because you have listed one column in your SELECT statement
that is not encapsulated in the COUNT function, you must use a GROUP BY
clause. The department field must, therefore, be listed in the GROUP BY
section.