Select the correct count of the results in a result set. (First, compose a query with a WHERE clause to show a large result set that meets a certain characteristic. Then, modify the query to show only the count of results in the result set.)
There are times when we may need to count the number of records or rows that fit certain criteria or parameters. The COUNT function can help. If you remember back to the SELECT clause with the * option that returned all rows, in the COUNT function, if we pass the *, it will return the total number of rows that fit the criteria.
If we looked at a basic query to find the number of customers, the query would look like the following:
Not all databases will return a specific row count, but PostgreSQL does in the result set:
Using COUNT(*) in the query would work on all databases:
Instead of passing in the * to count all rows, you can also add the column name in the table to count the number of rows that contain non-NULL values in that column. For example, if we’re looking for the number of customers that have a value for the company, it would look like this:
We can verify this by checking for the companies that don’t have a null value:
WHERE company IS NOT null;
Another example could be looking at the count of the state as we do see there are some null values.
If we look specifically at the state column, we should notice some repeating items:
WHERE state IS NOT NULL
ORDER BY state;
For example, CA repeats a total of three times. There are other instances where you may want to find a count of all of the unique occurrences using the DISTINCT keyword. It would look like the following:
SELECT COUNT (DISTINCT state)
Perhaps we want to identify how many of our employees are supporting customers using the support_rep_id. We can use the following query using the COUNT function to do so:
SELECT COUNT(DISTINCT support_rep_id)