Adding a Trailing Record in SQL Query-CodePudding

I have a SQL query that reports invoice data daily, the customer has asked that we add a trailing record to the output each time with data like the number of records in the query, number of locations serviced, etc.

Is there a way to add this record with the aggregate data to my query results? I have never done anything like this in SQL before and have not been able to find any examples. Thank you.

CodePudding user response：

This is arguably something that should be taken care of by the consumer...

However, assuming you're on a supported version of Db2 for IBM i as your tag suggests...

Take a look at the ROLLUP GROUPING SETS of the GROUP BY clause

Basically something like so

select fld1, fld2, sum(fld3) as fld3Total
from mytable
group by rollup ((fld1,fl2))

Will provide a trailing row where fld1 and fld2 are NULL and the 3rd column will contain the total.

Note that becuase of the use of GROUP BY, if there are multiple rows in the source data with the same value for fld1 and fld2 you will only get one row in the result set for them with a summed fld3.

Lastly, note the double parentheses ((fld1,fld2)) in the group by. With only a single set you'd get a total anytime one of the grouped by column value changes, basically sub-totals. Which is likely not what you want.

CodePudding user response：

you can use union all to add additional rows to the output of your query.

Here is query which adds a row containing the total number of pieces ordered to a query which lists the detail lines of an order.

select  a.ordordnum, a.ordordincr, a.ordprod, a.ordtotpcs,  
        'detail' source                                     
from     dwhpf30d a                                         
where   a.ordordnum = 963152                                
union all                                                   
select   a.ordordnum, 99999, 'total pieces',                
        sum(a.ordtotpcs) ordtotpcs, 'totals' source         
from      dwhpf30d a                                        
where      a.ordordnum = 963152                             
group by   a.ordordnum                                      
order by  1, 2