• 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
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    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

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

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 © 2025 IT Jungle