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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Fred, thanks for hint but I am afraid that your solution is too cumbersome. Imagine I have SQL with "group by ... with rollup" statement, which works fine in production. Now I want to cover by DAO with Unit tests. In order for them to work over HSQL, I will have to inject another SQL into DAO (which will be a decomposition or rollup like you have shown), thus SQL to be used for production cannot be tested.
If that SQL decomposition is so easy, why cannot HSQL do it automatically internally? I won't say for all, but for me personally this functionality is more important than triggers and stored procedures. Pity that community cannot vote for features...
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
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
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.
Would be nice to have rollup/cube options supported in HSQL. Any plans to add them?
There are no current plans. Perhaps next year or so.
Really wanted feature. I vote for it!
You can already do this
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.20Fred, thanks for hint but I am afraid that your solution is too cumbersome. Imagine I have SQL with "group by ... with rollup" statement, which works fine in production. Now I want to cover by DAO with Unit tests. In order for them to work over HSQL, I will have to inject another SQL into DAO (which will be a decomposition or rollup like you have shown), thus SQL to be used for production cannot be tested.
If that SQL decomposition is so easy, why cannot HSQL do it automatically internally? I won't say for all, but for me personally this functionality is more important than triggers and stored procedures. Pity that community cannot vote for features...
Any major functionality like this is very time-consuming to implement. Commercial users have not so far shown any interest in funding the development.