How to Count with SQL
October 26, 2005 Ted Holt
SQL can count two ways–vertically and horizontally. Anybody who uses SQL for data retrieval should know both methods. If you use SQL and don’t know how to count both ways, you’re in luck, because I’m about to explain both techniques.
Let’s consider vertical counting first because it’s the easier method. To count vertically, use the COUNT function and the GROUP BY clause. To illustrate, I’ll count records in my favorite database file QIWS/QCUSTCDT. You can find this file on your system, but to save you the trouble, here’s the data we’ll be using.
LSTNAM INIT STATE CHGCOD Henning G K TX 3 Jones B D NY 1 Vine S S VT 1 Johnson J A GA 2 Tyron W E NY 1 Stevens K L CO 1 Alison J S MN 3 Doe J W CA 2 Thomas A N WY 2 Williams E D TX 1 Lee F L NY 2 Abraham M T MN 3
Here’s the SQL command to count the number of customers in each state.
select state, count(*) from qiws/qcustcdt group by state order by state
The * in the COUNT function stands for “all rows”. The GROUP tells which field(s) to summarize by. Here’s the result set.
STATE COUNT(*) CA 1 CO 1 GA 1 MN 2 NY 3 TX 2 VT 1 WY 1
Isn’t that easy? I told you that counting vertically is easy. The reason I call it counting vertically is because the results are displayed vertically.
You can summarize by more than one field. Let’s summarize by state within charge code.
select chgcod, state, count(*) from qiws/qcustcdt group by chgcod, state order by chgcod, state
Here’s the result of the query.
CHGCOD STATE COUNT ( * ) 1 CO 1 1 NY 2 1 TX 1 1 VT 1 2 CA 1 2 GA 1 2 NY 1 2 WY 1 3 MN 2 3 TX 1
The result set is accurate, but isn’t it ugly? If you want to make it look more civilized, humane, or otherwise respectable, you can count horizontally. To count horizontally, you don’t use the COUNT function. Instead use the SUM function and the CASE expression.
CASE has two forms.
CASE expression WHEN value THEN value ELSE value END CASE WHEN expression THEN value ELSE value END
You may have more than one WHEN-THEN combo in either form, but for counting, one WHEN-THEN is sufficient. You may use either form you like.
It’s time for an example. Here are the CASE expressions you can use to count customers from New York.
case state when 'NY' then 1 else 0 end case when state='NY' then 1 else 0 end
When the system reads a record, it determines whether or not the state field has a value of NY or not. If so, it returns a one. If not, it returns a zero. To add up the ones and zeros, use the SUM function.
sum(case state when 'NY' then 1 else 0 end)
Let’s look at a complete query. This SQL command counts the number of New York customers, Minnesota customers, and other customers for each charge code.
select chgcod, sum(case state when 'NY' then 1 else 0 end) as New_York, sum(case state when 'MN' then 1 else 0 end) as Minnesota, sum(case when state not in ('NY','MN') then 1 else 0 end) as Other from qiws/qcustcdt group by chgcod order by chgcod
Here’s the result set.
CHGCOD NEW_YORK MINNESOTA OTHER 1 2 0 3 2 1 0 3 3 0 2 1
There you have it. Horizontal counting is easy, too.