[Basic Grammar] SQL COUNT Function

The COUNT () function gives the number of rows that matches a specified criterion. 

Example

Find the total number of brands in the Brands table:

SELECT COUNT(*)
FROM Brands;

 

• Syntax 

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

 

• Add a WHERE cluase 

Example 

Find the number of food where Price is higher than $11:

SELECT COUNT (*)
FROM FOODS
WHERE Price > 11;

 

• Specify Column 

Instead of using asterix symbol(*), you can specify a column name and NULL values will not be counted. 

 

Example 

Find the number of the banrds wehre BrandName is not null: 

SELECT COUNT (BrandName)
FROM Brands;

Brands table does not have any NULL values, but in this case, we can insert a NULL value. 

Example 

UPDATE Brands 
SET BrandName = NULL 
WHERE BrandName = 'Chang';

 

• Ignore Duplicates 

You can ignore deplicates by using the DISTINCT keyword in the COUNT function. If DISTINCT is specified, rows with the same value will be counted as one. 

 

Example 

How many different temperature are there in the Weather table:

SELECT COUNT (DISTINCT Temperature)
FROM Weather;

 

• Use an Alias

Give the counted column a name by using the AS keyword.

 

Example 

Name the column "number of names":

SELECT COUNT(*) AS [number of names]
FROM Products;

 

Exercises 

Use the correct function to return the number of records in the "Products" table where the "Category" is set to 'Electronics'.

SELECT COUNT (*) AS "Number of Electronics Products"
FROM Products 
WHERE Category = 'Electronics';