Reputation: 595
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.
Upvotes: 5
Views: 9458
Reputation: 11016
I used it following way with reference from TrustNoOne's Answer .
select securityname,bal,Total from (
select 1 orderCol,sectorname, '#' || COALESCE (sectorname,'') as securityname ,'' bal ,'' as Total from tablename
group by sectorname --Title
union
select 2 orderCol,sectorname, securityname,bal,'' from tablename --Items
union
select 3 orderCol,sectorname, sectorname || '- Subtotal :' as securityname ,'',Sum(bal) as Total from tablename
group by sectorname --SubTotal
union
select 99 orderCol,'_' sectorname, 'Grand Total :' as securityname ,'', sum(bal) as Total from tablename
group by 1,2,3 --GrandTotal
)
Securityname | Quantity | Total |
---|---|---|
#Group1 : | ||
Item1 | 50.0 | |
Item2 | 30.0 | |
Group1- Subtotal | 80 | |
#Group2 : | ||
Item1 | 10.0 | |
Group2- Subtotal | 10 | |
Grand Total | 90 |
Upvotes: 0
Reputation: 1
Had a same problem myself - emulating subtotals
in SQLite3
, and here`s, what I came to:
with counter(numm)as(select 1 union all select numm+1 from counter where numm<34),
str(par,node)as(select 1, numm from counter where numm in(2,5,8,11)union
select 11, numm from counter where numm in(12)union
select 12, numm from counter where numm in(13,17,20)union
select 13, numm from counter where numm in(14,15,16)union
select 17, numm from counter where numm in(18,19)union
select 2, numm from counter where numm in(3,4)union
select 20, numm from counter where numm in(21)union
select 21, numm from counter where numm in(22,23)union
select 5, numm from counter where numm in(6,7)union
select 8, numm from counter where numm in(9,10)union
select null, numm from counter where numm in(1)),
struct(par,node,clevel)as(select par,node,0 from str where par is null union all select c.par,c.node,s.clevel+1 from str c join struct s on s.node=c.par)/*struct*/,
namez(namee,node)as(select 'Grandtotal', numm from counter where numm in(1)union
select 'Subtotal1', numm from counter where numm in(2)union
select 'Subtotal2', numm from counter where numm in(5)union
select 'Subtotal3', numm from counter where numm in(8)union
select 'Subtotal4', numm from counter where numm in(11)union
select 'Subtotal5', numm from counter where numm in(12)union
select 'Subtotal6', numm from counter where numm in(13)union
select 'Subtotal7', numm from counter where numm in(17)union
select 'Subtotal8', numm from counter where numm in(20)union
select 'Subtotal9', numm from counter where numm in(21)union
select 'value10', numm from counter where numm in(18)union
select 'value11', numm from counter where numm in(19)union
select 'value12', numm from counter where numm in(22)union
select 'value2', numm from counter where numm in(4)union
select 'value3', numm from counter where numm in(6)union
select 'value4', numm from counter where numm in(7)union
select 'value5', numm from counter where numm in(9)union
select 'value6', numm from counter where numm in(10)union
select 'value7', numm from counter where numm in(14)union
select 'value8', numm from counter where numm in(15)union
select 'value9', numm from counter where numm in(16)union
select 'valueN', numm from counter where numm in(23)union
select 'vaule1', numm from counter where numm in(3)),
some_random_values(node,val)as(
select node,
case node
when 3 then 10 when 4 then 33 when 6 then 123 when 7 then 2
when 9 then 321 when 10 then 202 when 14 then 2 when 15 then 88
when 16 then 56 when 18 then 17 when 19 then 345 when 22 then 99 when 23 then 9
else 0
end from str),
sval(par,node,val)as(select s.par,s.node,a.val from str s join some_random_values a on a.node=s.node),
recur(par,node,val)as(
select * from sval where par in(select par from str group by par having(node)>1)
union all
select b.pAR,b.node,a.val+b.val
from recur a join sval b on b.node = a.par)
select s.par,s.node,substr(' ',1,s.clevel*5)||n.namee name,v.val
from struct s join namez n on n.node=s.node
join(select par,node,sum(val)val from recur group by 1,2)v on v.node=s.node
order by s.node
Example might look a bit complicated. Main part starts with recur(par,node,val)
.
Runs fine on SQLite 3.9.1
.
Upvotes: 0
Reputation: 595
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.
Upvotes: 1