• 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
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    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

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

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