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. Recall that with the SELECT clause, the * option returns all rows. If we pass the * in the COUNT function, 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:
SELECT *
FROM customer;
Not all databases will return a specific row count in the result set, but PostgreSQL does:
Using COUNT(*) in the query, however, would work on all databases:
SELECT COUNT(*)
FROM customer;
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:
SELECT COUNT(company)
FROM customer;
We can verify this by checking for the companies that don’t have a null value:
SELECT company
FROM customer
WHERE company IS NOT null;
As another example, we could look at the count of the state column, which does contain some null values.
SELECT COUNT(state)
FROM customer;
If we look more closely at the state column, we notice some repeating items:
SELECT state
FROM customer
WHERE state IS NOT NULL
ORDER BY state;
For example, CA is repeated. There may be times when you 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)
FROM customer;
As a final example, 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)
FROM customer;
[MUSIC PLAYING] With the COUNT aggregate function you're able to count the number of rows that's going to be returned based on the data set. So in this case here, if we're selecting from the customer table, of course, by default, it does show a row count. However, this is not shown in all cases and all databases, in which you'd actually have to utilize the COUNT function.
So one of the easy ways to be able to count all the different rows that's going to be returned is adding the COUNT with parentheses, and then the star between. That'll count all the different rows associated with it. In this case here, it returns 59.
The other push that you can actually take is counting based on the primary key. In our case here, it's going to be customer_id. And the reason you'd actually do that in this case here is that every single customer_id, because it's a primary key, would have a value.
One key unique thing when it comes to the COUNT function is that it only counts rows that have a specific value associated with it. If there's a null value or empty value, it will not be as part of the count.
So if you take a look back at the data itself, we'll notice that in the company column, not all the rows actually have a value for it. So if we utilize a COUNT on the company, it'll only count the number of rows that have a value for the company. So in this case here, only 10 customers actually have a company associated with them.
[MUSIC PLAYING]
Source: Authored by Vincent Tran
