Rollup Function for H2 Database

Question

I am using H2 Embeded database for a desktop application development, H2 Database is having all same function as SQL Function. but i am stuck at one place to use ROLLUP function in H2 Database. Or is there any other replacement for ROLLUP in H2 Database.

My query need is:

i want to show my data as follow in H2 DATBASE

  Customer    Product       Price 
  -------------------------------
  Suryam      Shampo        100
              Rubber        150
              Glass         250
              Total         500----->Total
  Raghu       Bottel        60  
              Biscuit       180
              Total         240----->Total
              Grand Total   740----->Grand Total

Please someone Help me

Answer 1

I couldn't find anything in the H2 documentation mentioning support for ROLLUP with GROUP BY queries. The good news is that you can simulate this functionality using GROUP BY along with a series of unions.

SELECT Customer, Product, Price
FROM yourTable
UNION ALL
SELECT Customer, 'Total', SUM(Price)
FROM yourTable
GROUP BY Customer
UNION ALL
SELECT '', 'Grand Total', SUM(Price)
FROM yourTable
ORDER BY CASE WHEN Customer = '' THEN 1 ELSE 0 END,
         Customer,
         CASE WHEN Product = 'Total' THEN 1 ELSE 0 END,
         Product;

Output:

Here is a link to a demo in MySQL. Different database than yours, but the behavior of UNION and CASE should be the same in H2 as in MySQL.

Rextester