Am searching for a way to emulate the "rollup" available in other sql based dbms. To be clear, I know how to get running subtotals and this is not what I am after. I also know that I can probably do this programmatically. However, if it is possible to do with one (or a couple) of sql statements I would rather do that.
As an example, for a table like this: product_type, order_amount, date I would seek a report which shows all the data grouped by product type and then by date, with a row showing the subtotal of order_amount on each change in product type and date.
I am constrained to using sqlite on android. I know enough sql to probably hurt a db but clearly not enough to emulate rollup and at this point I'm thinking it is not possible.
Any insights (even if 'no its not possible') appreciated.
Here is a way that works to emulate rollup under sqlite. I stumbled upon an answer from 2007 on a database forum asking about subtotals and grand totals. I'll summarize here with my simplified case.
I have a table stocks which contains Ticker_Symbol(Text), Underlying(Text), and Quantity(Integer) among other fields. Those suffice for this example.
Rollup can be emulated by using the following call from sqlite:
select Ticker_Symbol,Underlying,Quantity from (
select '1' orderCol, Ticker_Symbol,Underlying,Quantity from stocks
union
select '2' orderCol, Ticker_Symbol, 'Subtotal' as Underlying, Sum(Quantity) as Quantity from stocks
group by Ticker_Symbol
union
select '99' orderCol, '_' as Ticker_Symbol, 'GrandTotal' as Underlying, sum(Quantity) as Quantity from stocks)
as t1 order by case when orderCol=99 then 1 else 0 end, Ticker_Symbol, orderCol;
This produces output similar to below:
|Ticker_Symbol |Underlying|Quantity| |-------------------|----------|--------| AAPL AAPL 500 AAPL AAPL 1000 AAPL AAPL 2000 AAPL Subtotal 3500 AAPL140222P00500000 AAPL 10 AAPL140222P00500000 Subtotal 10 IBM140322C00180000 IBM 25 IBM140322C00180000 Subtotal 25 R140222C00067500 R 10 R140222C00067500 Subtotal 10 VLCCF VLCCF 300 VLCCF VLCCF 2000 VLCCF Subtotal 2300 _ GrandTotal 5845
Unfortunately, I could not find a way to avoid using the Ticker_Symbol. Ideally, it would be nice to just replace the current Ticker_Symbol with 'Subtotal' (or GrandTotal) but that does not work. Also note the use of the "_" to assure that GrandTotal does indeed show up on the last row.
I hope this helps others and if anyone out there has a way of making it better, please add.