JFreeChart using numeric query ORACLE

Question

Good Evening!

I have a Table named SWIMMER, and need to find(in the NUMERIC field named TIME)the number of ocorrence of some values in a given interval(I am using Oracle10g). I need to show something like that:

23 higher or equal TIME Shows HorizontalBar with value 300
22,3 higher or equal TIME minor or equal 23 Shows HorizontalBar with value 140
21,6 higher or equal TIME minor or equal 22,3 Shows HorizontalBar with value 15
20,9 higher or equal TIME minor or equal 21,6 Shows HorizontalBar with value 3

Its possible to have a query to have a return like that?How could i assembly that query? (Note: minor or equal are the Symbols,i can not post here because it is giving errors everytime)

Best Regards,

A.B.Cade · Accepted Answer · 2013-04-17 19:52:57Z

1

Try something like this (Here is a sqlfiddle):

select case 
           when time >= 23 then '23 =< TIME'
           when time < 23 and time >= 22.3 then '23 > TIME >= 22,3'
           when time < 22.3 and time >= 21.6 then '22,3 > TIME >= 21,6'
           when time < 21.6 and time >= 20.9 then '21,6 > TIME >= 20,9'
           else '20,9 > TIME'
        end   || ' with value '|| count(*) v
from your_table
group by case 
           when time >= 23 then '23 =< TIME'
           when time < 23 and time >= 22.3 then '23 > TIME >= 22,3'
           when time < 22.3 and time >= 21.6 then '22,3 > TIME >= 21,6'
           when time < 21.6 and time >= 20.9 then '21,6 > TIME >= 20,9'
           else '20,9 > TIME'
         end

and the results:

21,6 > TIME >= 20,9 with value 8 
20,9 > TIME with value 4 
22,3 > TIME >= 21,6 with value 6 
23 > TIME >= 22,3 with value 15 
23 =< TIME with value 66

UPDATE: As DavidAldrige suggested you can have a subquery:

select intrvl || ' with value '|| count(*) v
from
(select case 
           when time >= 23 then '23 =< TIME'
           when time < 23 and time >= 22.3 then '23 > TIME >= 22,3'
           when time < 22.3 and time >= 21.6 then '22,3 > TIME >= 21,6'
           when time < 21.6 and time >= 20.9 then '21,6 > TIME >= 20,9'
           else '20,9 > TIME'
        end   intrvl, time
from t)
group by intrvl

And here is another demo

edited Apr 17, 2013 at 19:52

answered Apr 17, 2013 at 5:20

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

David Aldridge Over a year ago

+1: I think I'd probably push the case into a subquery and aggregate on the result to avoid repeating that complex expression though.

PaulHB Over a year ago

A.B.Cade, thanks for your input!I will study your code and coment later!

PaulHB Over a year ago

David Aldridge, how i can use subqueries in that example?I just wrote the smallest portion of my problem to simplify the answers, but sometimes, i will have more than four-five expressions to show in the barchart(sometimes 10).Many thanks!

A.B.Cade Over a year ago

Updated my answer to include @DavidAldridge's suggestion

Collectives™ on Stack Overflow

JFreeChart using numeric query ORACLE

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related