Subsummaries and details in html query

Classic Servoy
1

Is it possible to have a group of item listed with details and put their subsummary using SQL + html formatting?
Now I’m using this query to retrieve expenses types:

SELECT SUM(p.expense_amount), t.expense_type, t.expense_description
from balance p, expenses t
where p.yearnr = 2003 and p.expenseid = t.expenseid
group by p.expenseid, t.expense_type, t.expense_description
order by 2 asc

So I get

Apples Golden 5.50
Apples Stark 15.30
Pears Williams 8.00
Bananas Chiquita 3.20
Total: 32.00
Which is fine, but how can I have

Apples Golden 5.50
Apples Stark 15.30
Apples total: 20.80
Pears Williams 8.00
Pears total: 8.00
Bananas Chiquita 3.20
Bananas total: 3.20
Total: 32.00

I thought it could be possible using a lot of queries, but I’m sure there’s an easier way :D

2

There is no such thing in standard SQL, some databases do support very unrelational unstandarized reports like commands…MSSQL has for example COMPUTE and ROLLUP statements

3

Jan Blok:
There is no such thing in standard SQL, some databases do support very unrelational unstandarized reports like commands…MSSQL has for example COMPUTE and ROLLUP statements

I suspected it could have been a problem.
Anyway, I prefer to avoid non standard queries because I want to keep my solution as database independent as I can.

Thanks, Jan