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:
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
|