Q:

Sqlite: subtotals in own row aka "rollup"

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. 

A:

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. 

