Have Your Cake and Eat It, Too
August 1, 2007 Hey, Ted
|
Recently one of my superiors walked into my cubicle and asked for some information. I was in the middle of another task and didn’t want to be side-tracked, so I hurriedly ripped out an SQL query to satisfy his request. A little while later he was back in my office asking me to run a fresh copy of the query, and while I was at it, to total some of the columns. This last request threw me for a loop, so I used Query/400. Could I have satisfied his request with SQL? –D I thought I had covered this situation in a previous edition of Four Hundred Guru, but I’ve googled and googled and I can’t find it. Looks like this old geezer’s got some bad RAM. It’s a wonder I remember my kids’ names. SQL can list details or generate summary information, but not in the same SELECT. To pull this off, you need two SELECT statements–one to generate the detail lines and one to generate the totals. You can use UNION to combine them into one result set. Here’s an example that uses the QCUSTCDT file in library QIWS. SELECT LSTNAM, INIT, CUSNUM, CITY, STATE, BALDUE FROM QIWS/QCUSTCDT UNION SELECT 'ZZ', ' ', 0, ' ', ' ', SUM(BALDUE) FROM QIWS/QCUSTCDT ORDER BY 1 The first SELECT lists the records in the file. The second SELECT totals the balance-due column. Notice that I had to load the non-grouping fields with dummy values. I put a dummy ZZ value in the first column, to force the total to the bottom of the report. (ORDER BY 1 sorts on the first column.) I put blanks or zeros into the other non-grouping columns, depending on data type. This is the report I got. LSTNAM INIT CUSNUM CITY STATE BALDUE Abraham M T 583,990 Isle MN 500.00 Alison J S 846,283 Isle MN 10.00 Doe J W 475,938 Sutter CA 250.00 Henning G K 938,472 Dallas TX 37.00 Johnson J A 938,485 Helen GA 3,987.50 Jones B D 839,283 Clay NY 100.00 Lee F L 192,837 Hector NY 489.50 Stevens K L 389,572 Denver CO 58.75 Thomas A N 693,829 Casper WY .00 Tyron W E 397,267 Hector NY .00 Vine S S 392,859 Broton VT 439.00 Williams E D 593,029 Dallas TX 25.00 ZZ 0 5,896.75 You can also include subtotals. Here’s a different query over the same file. This time the file is sorted on state abbreviation. There are three selects. The first and last are similar in function to the SELECT clauses in the previous example. The middle SELECT generates subtotals by state. Notice the last column of the report. It is blank for detail lines, one asterisk for a state subtotal, and two asterisks for the grand total. The stars have two purposes. They allow me to sort the report in the proper sequence (column 6 is the second sort field), and they make the subtotals and grand totals stand out. SELECT STATE, LSTNAM, INIT, CUSNUM, BALDUE, ' ' FROM QIWS/QCUSTCDT UNION SELECT STATE, ' ', ' ', 0, SUM(BALDUE), '*' FROM QIWS/QCUSTCDT GROUP BY STATE UNION SELECT 'ZZ', ' ', ' ', 0, SUM(BALDUE), '**' FROM QIWS/QCUSTCDT ORDER BY 1,6 Here’s the report. STATE LSTNAM INIT CUSNUM BALDUE Constant value CA Doe J W 475,938 250.00 CA 0 250.00 * CO Stevens K L 389,572 58.75 CO 0 58.75 * GA Johnson J A 938,485 3,987.50 GA 0 3,987.50 * MN Abraham M T 583,990 500.00 MN Alison J S 846,283 10.00 MN 0 510.00 * NY Jones B D 839,283 100.00 NY Lee F L 192,837 489.50 NY Tyron W E 397,267 .00 NY 0 589.50 * TX Henning G K 938,472 37.00 TX Williams E D 593,029 25.00 TX 0 62.00 * VT Vine S S 392,859 439.00 VT 0 439.00 * WY Thomas A N 693,829 .00 WY 0 .00 * ZZ 0 5,896.75 ** These reports are by no means elegant, but I learned years ago that people who are looking for numbers don’t usually care what the report looks like. –Ted
|
