How To Use SQL Count Functions

Introduction

The SQL count function is a simple function yet it has important uses when combined with the SQL select statement. The SQL count function is used to count the number of items from a selection. This would for example be that if there is a school with a certain number of students, the SQL count function could be used to easily get the total number of students from the existing records.

Without this function you would probably have to iterate through l the records while increasing a counter to add up the students as you go along. The other unreliable way would be to look at the index of the last student in the records and assume that that is the total number of students in the class.

This however in not feasible as indexes are usually unique numbers when a student leaves the school it could be that their number is never taken up by another student. This would mean that year in year out the total number index digits would keep increasing but not necessarily the number of students.

The SQL count function therefore would be used to solve this problem. Let us have a look at the syntax of this SQL function.

The SQL Count Syntax

The SQL count function can be written out in two basic ways. The first one using a simple expression as its argument and the other using a compound expression as its argument. Here is the syntax of this function with a simple expression:

COUNT(expression)

Where expression is the name of either all the columns in form of an asterisk “*” or the names of the individual columns.

The SQL count function with compound expressions as arguments can be written in this format.

COUNT(DISTINCT expression,[expression…])

Where the DISTINCT construct instructs the function not to count more than one occurrence of identical rows. The expression in square brackets simply means that the expression is optional and the ellipses mean that you theoretically can add as many rows as expressions according to the number of rows in your tables.

Next we look at how you can use this function in your SQL statements.

How The Count Function is Used in Your SQL

Assuming we have a list of student names, their birthdays and courses they attend, and we want to find the number of students that attend the Physics course, the statement below would make this possible.

SELECT COUNT(*) FROM students WHERE course = ‘Physics’;

If there are 50 students taking the physics course then the result would be an integer 50.

You can see the COUNT() function hides the complexity of how this is done. Here is another example with a different expression.

SELECT COUNT(birthday), COUNT(*) FROM students WHERE course = ‘Physics’;

Assuming we had two students that did not have their birthday records in the database, the result would be:

COUNT_birthday = 48

COUNT_* = 50

What just happened here? This simply tells us that the COUNT() function only counts entries that are not NULL except when you use the “*” as the expression.

When using Compound expressions in the SQL count function, this would be the way to do it.

SELECT COUNT(DISTINCT student_name, birthday) FROM student WHERE course = ‘Physics’;

This would be results based on whether the users share names, and whether any of the fields listed contains NULL entries.

Conclusion

There you have it. You have seen how simple the SQL count function is and how it works. This function can be implemented in every database management system that supports the SQL statements for accessing and manipulating data.

You can find other web related tips such as this sample code for JavaScript drop down menus and insight into Structured vs. Object-Oriented programming.