Three Powerful SQL Words

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