Thread: using to_number() in a select query with ranges

I want to extract data for graphing from a table, but the field containing the
data (value) is a text field as it has both numbers and text (that's how it
is delivered to me but I have created a view which from which I can know
which fields have the numbers and I can select the type of number I want (in
this case a pathology result hba1c, by one of the keys in that view).

I want to end up with a result containg the label for the x-axes of a graph,
the count of the type of value I'm after and the order to display the bars
in.

If I use this syntax within the query it returns the text as a number:

select  count (to_number("value",'9D9')) as the_number where fk_lu_request =
872 ;

But I want to be able to split up all the results into ranges eg <6, 6-7, 7-8
>8 etc.

I want to do the equivalent of this sort of concept.

select
      '6-6.5' as XAxes_text, count (value) as count, ,'1' as display_order
      From clin_requests.vwResults  where fk_lu_request = 872
    and to_number("value",'9D9'))  between 6-7;

but this dosn't work, and I've obviously got the syntax wrong. I've read the
docs and tried dozens of permutations to no avail.

Any help appreciated.

thanks.

richard

> select  count (to_number("value",'9D9')) as the_number where fk_lu_request =
> 872 ;
>
> But I want to be able to split up all the results into ranges eg <6, 6-7, 7-8
>>8 etc.
>
> I want to do the equivalent of this sort of concept.
>
> select
>      '6-6.5' as XAxes_text, count (value) as count, ,'1' as display_order
>      From clin_requests.vwResults  where fk_lu_request = 872
>    and to_number("value",'9D9'))  between 6-7;
>
> but this dosn't work, and I've obviously got the syntax wrong. I've read the
> docs and tried dozens of permutations to no avail.
>

Might be a little easier battle if you use trunc() where you have
'between 6-7'....
so maybe trunc(to_number("value",'9D9'))

Here is one that seems similar to your intent that gets the count on
archery scoring, used for a bar graph.

SELECT "cnt_score"."val" AS val, count("cnt_score"."val") AS counter
FROM "examples"."cnt_score" GROUP BY "cnt_score"."val" ORDER BY val
ASC