If you have worked with relational databases (RDBMS) for any length of time, you have almost certainly utilized the SQL COUNT() function. As such, you are no doubt already aware that the COUNT() function returns the number of rows or columns in a table, as filtered by the criteria specified in the WHERE clause. Its flexible syntax and widespread support makes it one of the most versatile and useful functions in SQL. In today's blog, we'll take a look at its many permutations and learn how to obtain a variety of counts.
One Function, Many Input Parameter Variations
As an ANSI SQL function COUNT() accepts parameters in the general SQL 2003 ANSI standard syntax. Having said that, different database vendors may have different ways of applying the COUNT() function. MySQL, PostgreSQL, and Microsoft SQL Server all follow the ANSI SQL syntax:
COUNT(*) COUNT( [ALL|DISTINCT] expression )
Meanwhile, the DB2 and Oracle syntax differs slightly:
COUNT ({*|[DISTINCT] expression}) OVER (window_clause)
In this blog, we'll be focusing on the SQL 2003 ANSI standard syntax. Here are what all of the input parameters mean:
- ALL: As its name implies, ALL applies the COUNT to all values so that it returns the number of non-null values.
- DISTINCT: Ignores duplicate values so that COUNT returns the number of unique non-null values.
- expression: An expression made up of:
- a single constant, variable, scalar function
- a column name
- part of a SQL query that compares values against other values
- *: COUNTs all the rows in the target table whether or not they include NULLs.
A Practical Example
To sample some of the various syntax permutations and their effects on COUNT output, let's apply the COUNT() function to the following employees table, shown in Navicat for MySQL:
Now, here's a query that counts several things:
- the total number of employees
- the number of managers
- the number of non-managers
- the number of departments
As the above query demonstrates, obtaining different counts is all about how in how you use the COUNT() function. In terms of when to use a specific column name as opposed to the asterisk, note that the former will not count nulls, whereas the latter typically will, because it includes all columns. As to when to use DISTINCT, consider using it for columns that have repeated values, which tends to include columns defined as non-unique and/or not the Primary Key.
Conclusion
The SQL COUNT() function's flexible syntax and widespread support makes it one of the most versatile and useful functions in SQL. Speaking of syntax, if you ever find it difficult to remember the COUNT() function's syntax, you can let Navicat remind you! The auto-complete suggestion list not only provides table and column names, but also stored procedures and functions, including COUNT(). You'll find not one, but two versions of it: one for simple usage and another for more complex uses:
