Can hsldb support rollup or something like?

Help
Peng Wang
2004-11-01
2014-03-20
  • Peng Wang
    Peng Wang
    2004-11-01

    Hi all,
    I want to make some statistic computing, but I cannot find any related topic in documentation or maillist. e.g. how can I implement the following statement(oracle 8i) in hsqldb?
    select to_char(return_date,'MONTH'), name, sum(fee)
    group by rollup(to_char(return_date,'MONTH'),name)

    Many Thanks

    Peng

     
    • Fred Toussi
      Fred Toussi
      2004-11-02

      HSQLDB doesn't support ROLLUP.

      You may be able to write a UNION which has the different rows and use another select with a GROUP BY clause around the UNION to order the results.

       
  • Dmitry Katsubo
    Dmitry Katsubo
    2013-04-16

    Would be nice to have rollup/cube options supported in HSQL. Any plans to add them?

     
  • Fred Toussi
    Fred Toussi
    2013-04-16

    There are no current plans. Perhaps next year or so.

     
  • Dmitry Katsubo
    Dmitry Katsubo
    2014-03-20

    Really wanted feature. I vote for it!

     
  • Fred Toussi
    Fred Toussi
    2014-03-20

    You can already do this

     
  • Fred Toussi
    Fred Toussi
    2014-03-20

    Example:

      CREATE TABLE JOBS (CLIENTID INT, JOBTYPE INT, FEE DECIMAL(10,2))
    
      INSERT INTO JOBS VALUES 11, 2, 12.40
      INSERT INTO JOBS VALUES 11, 2, 11.10
      INSERT INTO JOBS VALUES 11, 3, 2.10
      INSERT INTO JOBS VALUES 13, 3, 2.10
      INSERT INTO JOBS VALUES 13, 3, 1.10
    
      WITH JOBSTATS(CLIENTID, JOBTYPE, FEE) AS (SELECT CLIENTID, JOBTYPE, SUM(FEE) 
           FROM JOBS GROUP BY CLIENTID, JOBTYPE)
      SELECT CLIENTID, CAST(JOBTYPE AS VARCHAR(10)) AS JOBTYPE, FEE FROM JOBSTATS
         UNION
      SELECT CLIENTID, '', SUM(FEE) FROM JOBSTATS GROUP BY CLIENTID ORDER BY CLIENTID, JOBTYPE DESC
    
     CLIENTID JOBTYPE FEE   
     -------- ------- ----- 
     11       3       2.10  
     11       2       23.50 
     11               25.60 
     13       3       3.20  
     13               3.20