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.