Online College Courses for Credit

+
COUNT to Count Records

COUNT to Count Records

Rating:
(0)
Author: Sophia Tutorial
Description:

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.)

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the COUNT function to count records in two parts:

  1. Getting Started
  2. Counting Distinct Values

1. Getting Started

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:

SELECT *
FROM customer;

Not all databases will return a specific row count, but PostgreSQL does in the result set:

File:11290-1335-1.png

Using COUNT(*) in the query would work on all databases:

SELECT COUNT(*)
FROM customer;

File:11291-1335-2.png

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;

File:11292-1335-3.png

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;

File:11293-1335-4.png

Another example could be looking at the count of the state as we do see there are some null values.

SELECT COUNT(state)
FROM customer;

File:11294-1335-5.png


2. Counting distinct values

If we look specifically at the state column, we should notice some repeating items:

SELECT state
FROM customer
WHERE state IS NOT NULL
ORDER BY state;

File:11295-1335-6.png

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)
FROM customer;

File:11296-1335-7.png

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;

File:11297-1335-8.png


try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The COUNT function allows us to count the number of rows given a set of filters or the number of non-NULL values in a column.