• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL Bar Charts

    April 13, 2011 Hey, Ted

    A while back I had a problem that appears to be quite common, but just rare enough that it’s a little challenging when it hits you. Often in our roles as database administrators we need to determine various figures, such as how many key values in a table are used up in case we need to extend the field. Or sometimes we just to provide a statistical representation of data. What I needed was to produce a histogram using SQL.

    Let’s say you have an array of sample statistics and you want to break them into groups of an arbitrary size. Then you want to count the number in each group. Using the modulus function (remainder after division) in SQL you can determine the start value of each group as:

    value - mod(value, group_size)
    

    Then you only need to count them and group them.

    select value-mod(value,500), count(*)
      from MyFile
     where My_Selection_Criteria_is_true
     group by value-mod(value,500)
    

    This produced a result in my case like:

    Limit  Count
    =====  =====
        0    480
      500    290
    1,000    134
    1,500    247
    2,000    268
    2,500    287
    3,000    259
    3,500    230
    4,000    232
    4,500    221
    

    This is a pretty darn cool use of the mod() function. It could be glorified even more being wrapped in a procedure that generically opens a passed file, field, and group size.

    –Daz

    Daz’s interesting idea–which included the word histogram–got me to thinking.

    Everybody likes pictures. Businesses take the numbers IT gives them and change them into all sorts of pretty graphs and charts. Why can’t SQL do the same, albeit it at a rudimentary level, in order to call attention to certain rows in the result set?

    Here’s a report that shows customers and how much they spent with us.

    CUSTOMER             AMOUNT
    ============     ==========
    Bill                 489.00
    Bob                1,276.00
    Sue                1,750.00
    Jim                1,355.00
    Henry                 67.00
    Kyle               3,000.00
    Betsy              2,600.00
    Joe                1,688.00
    Hector             1,250.00
    Ray                  749.00
    Flossie            2,610.00
    Pete               1,399.00
    

    Nothing stands out, so let’s look at it another way. Here I print an X for each 500 units of currency they spent with us. The amounts are rounded, so one X really means 250 to 749 currency units. Also, I used a different way of calculating from the one Daz used, just to show another way to group the data.

    CUSTOMER             AMOUNT   BAR
    ============     ==========   =======
    Bill                 489.00   X
    Bob                1,276.00   XXX
    Sue                1,750.00   XXXX
    Jim                1,355.00   XXX
    Henry                 67.00
    Kyle               3,000.00   XXXXXX
    Betsy              2,600.00   XXXXX
    Joe                1,688.00   XXX
    Hector             1,250.00   XXX
    Ray                  749.00   X
    Flossie            2,610.00   XXXXX
    Pete               1,399.00   XXX
    

    The highlights and lowlights stand out, don’t they?

    Here’s another view of the same data. This time I count the number of customers in each range of sales values.

    with Summary as
       (select int(round((amount/500),0)*500) as Category
          from salesdata)
    select Category, count(*) as Count,
           repeat ('X', count(*)) as Bar
      from Summary
     group by Category
     order by Category
    
         CATEGORY     COUNT   BAR  
       ==========     =====   ==========
                0         1   X    
              500         2   XX
            1,500         5   XXXXX
            2,000         1   X
            2,500         2   XX
            3,000         1   X
    

    It’s obvious that most customers spend around 1,500 units of currency with us.

    There are probably a lot of other things we could do with SQL. We just need a little more imagination.

    –Ted



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    SEQUEL Software:  FREE Webinar: Track Key Business Metrics at a Glance. April 27
    BlueFountain Technologies:  Replace outdated "green screen" supply chain software
    COMMON:  Join us at the 2011 Conference & Expo, May 1 - 4 in Minneapolis, MN

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    IBM Connects WebSphere MQ and Sterling MFT Offerings IBM Doubles Up Power7 Blade Sockets, Cranks Power 750 Clocks

    Leave a Reply Cancel reply

Volume 11, Number 12 -- April 13, 2011
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
COMMON

Table of Contents

  • A View of a View of a View
  • SQL Bar Charts
  • Admin Alert: The Four Faces of Software Vendors During a Hardware Upgrade

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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