Multiple condition query report in JasperReports

[mikij]

 Hi,

 

I need to define a report that have predefined multiple ranges for rows. I have simple table Orders, and I need to define report like this, e.g. for a year:

 

------------------------------------------------------------------------------                            

Order price     |        Value           |        Percent       |                 

-----------------------------------------------------------------------------                 

0-1000           |   someValue$      |   % (of all values) |                    

-----------------------------------------------------------------------------                

1000-2000      |   someValue$      |   % (of all values) |                     

-----------------------------------------------------------------------------                   

etc., to 9000-10000.                                               |

-----------------------------------------------------------------------------

 

I tried to do this with select CASE WHEN (price > 0 and price < 1000) then '0-1000' END as price ...group by price, but in such case I cannot see those ranges (rows) where I don't have value, and for these rows value and percent should be 0 in Value and Percent columns. Do you have ANY HINT how can I define report like this in JasperReports (iReport), because I don't have :-( ?

 

--

Thx in advance!!

[mikij]

 I defined 12 queries like this, for every range:

 

SELECT

     '0-1000' as Order price, sum(Value)

     from Order...

    where price >= 0 and price <=1000

union all

SELECT

     '1000-2000' as Order price, sum(Value)

     from Order...

    where price >= 2000 and price <=1000

union all

...

but I don't like this solution, because in this case I need to have 12 such queries, and I'm afraid that it's too much. Anyone have maybe some better solution ?