SQL Statement Fundamentals: The COUNT Function
Hello, hello, hello SQL fans! (Or gracious friends and family perusing the site. 🙂 ) The journey into learning SQL continues, and today we’ll cover the COUNT function. Jumping right into it, here’s our working definition of the SQL COUNT function: The COUNT function should return the number of input rows that match a […]
Hello, hello, hello SQL fans! (Or gracious friends and family perusing the site. 🙂 ) The journey into learning SQL continues, and today we’ll cover the COUNT function. Jumping right into it, here’s our working definition of the SQL COUNT function:
The COUNT function should return the number of input rows that match a specific condition of a query.
Rather, this would appear to work similarly in concept to the COUNTIF(s) formula(s) in Excel.
COUNT Statement Syntax Examples
Here’s what a simple COUNT SQL statement might look like:
1. Basic COUNT (*) FROM SQL Statement
SELECT COUNT (*) FROM table;
Breaking it down a bit, the COUNT () function returns the number of rows returned by a SELECT clause. When you apply the COUNT () statement to the entire table, pgAdmin/PostgreSQL will scan the entire table in a sequential manner.
Additionally, you can specify a certain column count in your COUNT statement for better readability:
2. COUNT (column) FROM SQL Statement
SELECT COUNT(column) FROM table;
Similar to the COUNT(*) function, the COUNT(column) function returns the number of rows returned by a SELECT clause. However, if you have empty or NULL values, the COUNT function will not take those into account.
3. COUNT (DISTINCT column) FROM SQL Statement
If we do a bit of application from our past learnings, we can make a COUNT with DISTINCT SQL statement:
SELECT COUNT(DISTINCT column) FROM table;
Applying the COUNT SQL Function
Alright, so let’s work our way toward applying what we’ve learned. To start, let’s do the traditional probe of the table before diving in, to familiarize ourselves. Below, we’ve done a basic,
SELECT * FROM address;
As we get familiar with the table, we can scroll down and see this particular table has 605 rows in it. This will be a reference point as we continue.
Moving forward, we’ll execute a basic SELECT COUNT (*) FROM address; SQL query. Below, you’ll see a slightly different result was returned. Inst3ead of 605 rows, 603 was returned. At this point, kindly reference our note about empty / NULL values being excluded from the COUNT function.
We’ve established a general proof of concept for the SELECT COUNT statement, considering the reduced load on the server and yourself, for a quick count. Let’s now try calling specific columns. In our first exploration of the address table, we saw a number of columns, including the district. Let’s say we want to get a count for how many districts/states our customer base covers.
Above, a count of 378 distinct district values has been returned. Wow, what coverage!
A quick aside for future usage, you can also nest the column reference in its own set of parentheses, as shown and returned below.
There you have it! We’ve learned a bit about the COUNT function, what it does and how to use it. It will likely come in handy for future articles, particularly when we delve into group-by excercises. If you missed it, here’s the previous article on learning how to use SELECT WHERE and another recent article on learning how to use SELECT DISTINCT. Also, to start from the beginning, here’s my running list of articles on how to learn SQL. Cheers!