• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored By
    ADVANCED SYSTEMS CONCEPTS

    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.

    www.asc-iseries.com

    For LANSA, 3-Way Product Data Synch is as Easy as ABI EC Kronos Tackles Unscheduled Absenteeism with Labor Software

    Leave a Reply Cancel reply

Volume 5, Number 40 -- October 26, 2005
THIS ISSUE
SPONSORED BY:

Advanced Systems Concepts
Linoma Software
WorksRight Software

Table of Contents

  • 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

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Big Blue Finally Brings IBM i To Its Own Public Cloud
  • Building A Positive Culture of Learning On IBM i
  • Guru: Change XML Elements in SQL
  • Four Hundred Monitor, February 18
  • IBM i PTF Guide, Volume 21, Number 7
  • Guru Classic: Triggers – Allow Repeated Change
  • Guru Classic: Who Needs Custom Perspectives In RDi?
  • Guru Classic: A Bevy of BIFs — %CHAR, %EDITC and %EDITW
  • Settling In With IBM i For The Long Haul
  • Domino And Notes 10 Finally Come To IBM i

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2017 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.