sq17

A commonly used aggregate function in SQL is COUNT(). COUNT() returns the number of rows that match the given criteria.

COUNT(*)

If we only want to see how many records are in a table (but not actually view those records), we could use COUNT(*). COUNT(*) returns everything — including null values and duplicates.

SQL statement

SELECT COUNT(*) FROM Individual;

Source Table

IndividualId	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike
6	Bono		u2

Result

6

COUNT(column name)

If we want to see how many non-null values are in a given column, we use COUNT(column name) where column name is the name of the column we want to test.

SQL statement

SELECT COUNT(LastName) FROM Individual;

Source Table

Id	FirstName	LastName	UserName
1	Fred	Flinstone	freddo
2	Homer	Simpson	homey
3	Homer	Brown	notsofamous
4	Ozzy	Ozzbourne	sabbath
5	Homer	Gain	noplacelike
6	Bono		u2

Result

5

Combining COUNT & DISTINCT

If we only want to see how many unique names are in the table, we could nest the DISTINCT inside a COUNT function.

SQL statement

SELECT COUNT(DISTINCT(FirstName)) FROM Individual;

Result

4