• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Three Powerful SQL Words

    December 4, 2013 Ted Holt

     

    Louisa May Alcott said, “I like good strong words that mean something.” So do I, especially when they’re good strong SQL words. It gives me great pleasure today to share three powerful SQL words with you.

    Consider the following summary query:

    select d.ItemNumber,
           sum(d.Quantity) as Qty,
           sum(d.Quantity * d.Price) as Extended
      from InvoiceLines as d
     group by d.ItemNumber
     order by 1
    

    Here’s the result set:

    ITEMNUMBER      QTY      EXTENDED
      A-1             8         88.00
      A-3             7         35.00
      A-7             7         52.00
      B-1            26         51.25
      Z-3             2         18.00
    

    Standard stuff, yet very powerful. It beats writing an RPG program to read a file, check for a control break on item number, sum the quantity and extended price, and write the results to some device. Unquestionably, SQL is powerful.

    Would you like SQL to be a little more powerful? (The correct answer is “Yes!”) Look at this query. Notice the next-to-last line, in red.

    select d.ItemNumber,
           sum(d.Quantity) as Qty,
           sum(d.Quantity * d.Price) as Extended
      from InvoiceLines   as d
     group by d.ItemNumber
     with rollup
     order by 1
    

    And here’s the result set. Look at the last line.

    ITEMNUMBER      QTY      EXTENDED
      A-1             8         88.00
      A-3             7         35.00
      A-7             7         52.00
      B-1            26         51.25
      Z-3             2         18.00
        -            50        244.25
    

    Those two powerful words, WITH ROLLUP, were enough to sum up the item groups into grand totals.

    Notice the first column of the last row. The summary row for all items cannot have an item number, so the item number column is null.

    Let’s take it a step farther.

    Here’s a similar query, grouped on two levels–item number within invoice date.

    select h.InvoiceDate, d.ItemNumber,
           sum(d.Quantity),
           sum(d.Quantity * d.Price) as Extended
      from InvoiceHeaders as h
      join InvoiceLines   as d
        on h.InvoiceNumber = d.InvoiceNumber
     group by h.InvoiceDate, d.ItemNumber
     order by 1,2
    

    Here’s what you would expect to see.

    INVOICEDATE  ITEMNUMBER   SUM     EXTENDED
     11/27/13      A-3          7        35.00
     11/27/13      A-7          1         8.00
     11/27/13      B-1         14        26.50
     11/28/13      A-1          3        33.00
     11/28/13      A-7          4        30.00
     11/28/13      B-1          5        10.00
     11/28/13      Z-3          2        18.00
     11/29/13      A-1          5        55.00
     11/29/13      A-7          2        14.00
     11/29/13      B-1          7        14.75
    

    Add those two powerful words.

    select h.InvoiceDate, d.ItemNumber,
           sum(d.Quantity),
           sum(d.Quantity * d.Price) as Extended
      from InvoiceHeaders as h
      join InvoiceLines   as d
        on h.InvoiceNumber = d.InvoiceNumber
     group by h.InvoiceDate, d.ItemNumber
     with rollup
     order by 1,2
    

    Look at what you get.

    INVOICEDATE  ITEMNUMBER   SUM     EXTENDED
     11/27/13      A-3          7        35.00
     11/27/13      A-7          1         8.00
     11/27/13      B-1         14        26.50
     11/27/13      -           22        69.50
     11/28/13      A-1          3        33.00
     11/28/13      A-7          4        30.00
     11/28/13      B-1          5        10.00
     11/28/13      Z-3          2        18.00
     11/28/13      -           14        91.00
     11/29/13      A-1          5        55.00
     11/29/13      A-7          2        14.00
     11/29/13      B-1          7        14.75
     11/29/13      -           14        83.75
     -             -           50       244.25
    

    GROUP BY builds summary totals by item within date. WITH ROLLUP builds date totals and grand totals. Notice the nulls in the rows that were built by the rollup.

    Today’s third powerful word is GROUPING. This powerful function accepts a column name and returns 0 or 1 to indicate whether or not WITH ROLLUP generated a summary row over that column. I think of the zero as false and the 1 as true, even though SQL has no Boolean type.

    In this example, GROUPING is used in the SELECT and ORDER BY clauses.

    select case when grouping(h.InvoiceDate)=0
                   then char(h.InvoiceDate)
                   else 'Grand total' end as Date,
           case when grouping(d.ItemNumber)=0
                   then d.ItemNumber
                   else 'Total for date' end as Item,
           sum(d.Quantity),
           sum(d.Quantity * d.Price) as Extended
      from InvoiceHeaders as h
      join InvoiceLines   as d
        on h.InvoiceNumber = d.InvoiceNumber
     group by h.InvoiceDate, d.ItemNumber
      with rollup
     order by grouping(h.InvoiceDate), h.InvoiceDate,
              grouping(d.ItemNumber) , d.ItemNumber
    

    GROUPING(H.INVOICEDATE) is 1 when the row is a summary of invoice date summaries. The only row for which that is true is the last one.

    GROUPING(D.ITEMNUMBER) is 1 when the row is a summary of item number within invoice date. That is true for one row per date.

    Here is the result set.

    DATE         ITEM            SUM           EXTENDED
    11/27/13     A-3               7              35.00
    11/27/13     A-7               1               8.00
    11/27/13     B-1              14              26.50
    11/27/13     Total for date   22              69.50
    11/28/13     A-1               3              33.00
    11/28/13     A-7               4              30.00
    11/28/13     B-1               5              10.00
    11/28/13     Z-3               2              18.00
    11/28/13     Total for date   14              91.00
    11/29/13     A-1               5              55.00
    11/29/13     A-7               2              14.00
    11/29/13     B-1               7              14.75
    11/29/13     Total for date   14              83.75
    Grand total  Total for date   50             244.25
    

    Including GROUPING in the ORDER BY clause immediately before the control field to which it refers puts the control total after the rows it summarizes. Think about it. GROUPING is 1 for the total and 0 for the rows that make up the total. Therefore, GROUPING for invoice date immediately precedes invoice date, and grouping for item number immediately precedes item number.

    I used GROUPING in the SELECT clause to replace the generated nulls with appropriate non-null values. When a row is a summary of all dates, the date is “Grand total”. When a row is a summary of items within a date, the item text is “Total for date”.

    So these three words–WITH ROLLUP and GROUPING–replace many lines of RPG. Whereas we used to have to write RPG programs with control break calculations to get summary data, SQL does it quickly and easily for us.

    That’s all well and good, but I’ve been writing report programs for over 30 years, and I know that most reports include not only summary figures, but the details that make up the summaries. I published one method a few years ago. (See Have Your Cake and Eat It, Too.) I wondered how I might be able to take advantage of these OLAP features, and yet build a detailed report with control totals. I learned that it’s possible.

    To create a detailed report with summaries requires the union of two queries. The first query builds the detail lines. The second one builds the summaries.

    select 0 as GroupingInvoiceDate,
           0 as GroupingItem,
           0 as IsSummaryLine,
           char(h.InvoiceDate) as InvoiceDate,
           d.InvoiceNumber,
           d.LineNumber,
           d.ItemNumber,
           d.Quantity,
           d.Price,
           d.Quantity * d.Price as Extended
      from InvoiceLines as d
      join InvoiceHeaders as h
        on d.InvoiceNumber = h.InvoiceNumber
    union all
    select grouping(h.InvoiceDate),
           grouping(d.ItemNumber),
           1,
           case when grouping(h.InvoiceDate)=0
                   then char(h.InvoiceDate)
                   else 'Grand total' end as Date,
           0,
           0,
           case when grouping(h.InvoiceDate)=1
                    then ' '
                when grouping(d.ItemNumber)=1
                    then 'Total for date'
                else d.ItemNumber end,
           sum(d.Quantity),
           0,
           sum(d.Quantity * d.Price) as Extended
      from InvoiceHeaders as h
      join InvoiceLines   as d
        on h.InvoiceNumber = d.InvoiceNumber
     group by h.InvoiceDate, d.ItemNumber
     with rollup
     order by 1, 4, 2, 7, 3
    

    Here’s the result set.

             Date   Invoice Ln  Item   Qty  Price  Extend
    = = =  ========  =====  ==  ====== ===  =====  ======
    0 0 0  11/27/13  10001   1  A-3      5   5.00   25.00
    0 0 0  11/27/13  10005   1  A-3      2   5.00   10.00
    0 0 1  11/27/13      0   0  A-3      7    .00   35.00
    0 0 0  11/27/13  10004   1  A-7      1   8.00    8.00
    0 0 1  11/27/13      0   0  A-7      1    .00    8.00
    0 0 0  11/27/13  10001   2  B-1      4   2.00    8.00
    0 0 0  11/27/13  10002   1  B-1      3   2.25    6.75
    0 0 0  11/27/13  10003   1  B-1      2   2.50    5.00
    0 0 0  11/27/13  10005   2  B-1      2   1.50    3.00
    0 0 0  11/27/13  10006   1  B-1      3   1.25    3.75
    0 0 1  11/27/13      0   0  B-1     14    .00   26.50
    0 1 1  11/27/13      0   0  Total for date
                                        22    .00   69.50
    0 0 0  11/28/13  10009   1  A-1      3  11.00   33.00
    0 0 1  11/28/13      0   0  A-1      3    .00   33.00
    0 0 0  11/28/13  10008   2  A-7      4   7.50   30.00
    0 0 1  11/28/13      0   0  A-7      4    .00   30.00
    0 0 0  11/28/13  10009   2  B-1      5   2.00   10.00
    0 0 1  11/28/13      0   0  B-1      5    .00   10.00
    0 0 0  11/28/13  10007   1  Z-3      2   9.00   18.00
    0 0 1  11/28/13      0   0  Z-3      2    .00   18.00
    0 1 1  11/28/13      0   0  Total for date
                                        14    .00   91.00
    0 0 0  11/29/13  10011   2  A-1      5  11.00   55.00
    0 0 1  11/29/13      0   0  A-1      5    .00   55.00
    0 0 0  11/29/13  10011   3  A-7      2   7.00   14.00
    0 0 1  11/29/13      0   0  A-7      2    .00   14.00
    0 0 0  11/29/13  10010   1  B-1      4   2.00    8.00
    0 0 0  11/29/13  10011   1  B-1      3   2.25    6.75
    0 0 1  11/29/13      0   0  B-1      7    .00   14.75
    0 1 1  11/29/13      0   0  Total for date
                                        14    .00   83.75
    1 1 1  Grand total   0   0          50    .00  244.25
    

    Notice the first three columns. They’re there for only one reason–to sort the data. The first two columns are the results of the GROUPING functions, as mentioned earlier. I set them to zero for the detail lines. The third column is zero for detail lines and one for summary lines.

    ORDER BY sorts on these columns:

    • 1, which forces the grand total to the end of the data
    • 4, which sorts on invoice date
    • 2, which forces subtotals by date to the end of each day’s data
    • 7, which sorts on item number
    • 3, which sorts details for each control group ahead of group totals

    Note also that I loaded default values (zero, for numeric fields) in the totals for columns that had no summary figures, such as price.

    SQL has done a lot of work for me. All that’s left for me to do is to deliver the output to the user.

    I’ve given you three powerful SQL words. May they have a positive impact on your life.

     

    RELATED STORIES

    Treasury of new DB2 6.1 Features, Part 2: Grouping Sets and Super Groups

    Have Your Cake and Eat It, Too

     



                         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
    TL Ashford

    TL Ashford writes software to generate Barcode Labels and Forms for the IBM i.

    Our software products are the most feature-rich, cost-effective solutions available!

    TLAForms converts traditional IBM i spool files into high quality, professional forms that are output as PDF documents. A form is associated with a spool file and the form is designed. TLAForms then ‘watches’ the IBM i output queue for a new spool file to be generated. When a new spool file is generated, TLAForms reads the data from the spool file, places the data on the form, and outputs a PDF document. The PDF documents can be archived to the IFS, automatically emailed, and printed.

    Features:

    • Select Data directly from an IBM i Spool File
    • Burst IBM i Spool Files based on page or Spool File data
    • Add spool file data to form as Text using a wide variety of fonts and colors (the MICR font for printing checks is included in the software)
    • Add spool file data to form as bar code – including 2-dimensional bar codes PDF-417 and QRCode
    • Configure SQL statements to retrieve and use IBM i File data on forms
    • Utilize Actions to show or hide objects on the form based on data within the spool file
    • Import Color Graphics
    • Use Color Overlays
    • Create Tables
    • Forms can be archived to the IFS
    • Forms can be emailed automatically
    • Forms can be printed to any IBM i, Network or Windows printer
    • Forms are automatically generated when a new spool file is generated in the IBM i output queue
    • NO PROGRAMMING required
    • On-Line Video Training Library

    Learn more about TLAForms at https://tlashford.com/tlaforms/

    Barcode400 is native IBM i software to design and print labels directly from your IBM i in minutes! Compliance and RFID labeling is easy using Barcode400’s tools and templates.

    Features:

    • Software resides on the IBM i
    • IBM i security and Backup
    • Labels are centrally located on the IBM i
    • Label formats are available to all users the instant they are saved – even in remote facilities
    • GUI designer (Unlimited Users)
    • Generate Labels as PDF documents!
    • Print to 100’s of thermal transfer printers
    • Print to HP and compatible printers
    • Print labels interactively – No Programming Necessary!
      • OR Integrate into existing application programs to automatically print labels – Barcode400 has been integrated with nearly every ERP and WMS software available on the IBM i, including thousands of in-house written applications.
      • On-Line Video Training Library
      • Free Compliance Labels
      • Generate Checks using the MICR font
      • RFID Support (optional)
      • History / Reprint Utility
      • Integration Assistant
      • Low Cost (no tiered pricing)

    Learn more about Barcode400 at https://tlashford.com/barcode400/

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Abacus Solutions:  Let our IBM i experts take your infrastructure to the cloud! Linoma Software:  GoAnywhere Secure File Transfer. Simplify. Automate. Encrypt. Cybernetics:  Ditch the tape backup? Up to 4.3 TB/hr! Start saving now!

    More IT Jungle Resources:

    System i PTF Guide: Weekly PTF Updates
    IBM i Events Calendar: National Conferences, Local Events, and Webinars
    Breaking News: News Hot Off The Press
    TPM @ EnterpriseTech: High Performance Computing Industry News From ITJ EIC Timothy Prickett Morgan

    SMBs Lack Cohesive Digital Strategy, IBM Says IBM i Installed Base Dominated By Vintage Iron

    Leave a Reply Cancel reply

Volume 13, Number 23 -- December 4, 2013
THIS ISSUE SPONSORED BY:

ASNA
ProData Computer Services
WorksRight Software

Table of Contents

  • End-Of-Year Feedback
  • Three Powerful SQL Words
  • Admin Alert: Four Ways To Move An IBM i Partition, Part 1

Content archive

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

Recent Posts

  • Power10 Midrange Machine: The Power E1050
  • IBM Puts The Finishing Touches On PowerHA For IBM i 7.5
  • Guru: Regular Expressions, Part 2
  • Get Your Security Education, And Not From The School Of Hard Knocks
  • IBM i PTF Guide, Volume 24, Number 33
  • Power10 Entry Machines: The Power S1024 And Power L1024
  • Thoroughly Modern: Latest IT Trends – Bring Security, Speed, And Consistency To IT With Automation
  • Big Blue Unveils New Scalable VTL For IBM i
  • As I See It: Thank God It’s Thursday
  • IBM i PTF Guide, Volume 24, Number 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 © 2022 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.