Newsletters   Subscriptions  Forums  Store   Career  Media Kit  About Us  Contact  Search   Home 
Volume 5, Number 40 -- October 26, 2005

How to Count with SQL

by 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.

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.

 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.

   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

   WHEN expression THEN value 
   ELSE value 

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.

   1          2           0        3     
   2          1           0        3     
   3          0           2        1     

There you have it. Horizontal counting is easy, too.

Sponsored By

SEQUEL can be used for virtually ALL data access functions on the iSeries.

A Windows-based user interface makes it easy to design queries and reports.

SEQUEL offers executive dashboards, drill-down data analysis and run-time prompts to deliver important iSeries data to managers and other non-technical users.

E-mail and FTP delivery let you deliver information to remote users and servers.

Technical Editors: Howard Arner, Joe Hertvik, Ted Holt,
Shannon O'Donnell, Kevin Vandever
Contributing Technical Editors: Joel Cochran, Wayne O. Evans, Raymond Everhart,
Bruce Guetzkow, Marc Logemann, David Morris
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.


Advanced Systems Concepts
Linoma Software
WorksRight Software

Four Hundred Guru


How to Count with SQL

Those Stupid Quotation Marks!

Admin Alert: New TCP/IP Functions to Check Out When Upgrading to i5/OS V5

Correction: Bugged by the Bugged by Interactive Debugger Tip

The Four Hundred
Behind the Scenes at the Award-Winning iSeries Support Center

iSeries Sales Rebound 25 Percent in Q3

Sometimes You Have to Think--and Look--Inside the Box

As I See It: Listen Up, Kids

Four Hundred Stuff
iTera Says Out-of-Synch Conditions are a Thing of the Past

Raz-Lee Developing Native iSeries Antivirus Software

The Real World, Versus Real-World Load Testing

For LANSA, 3-Way Product Data Synch is as Easy as ABI EC

Four Hundred Monitor

Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc. (formerly Midrange Server), 50 Park Terrace East, Suite 8F, New York, NY 10034
Privacy Statement