Skip site navigation (1) Skip section navigation (2)

using to_number() in a select query with ranges

From: richard terry <rterry(at)pacific(dot)net(dot)au>
To: pgsql-novice(at)postgresql(dot)org
Subject: using to_number() in a select query with ranges
Date: 2009-01-27 20:50:13
Message-ID: 200901280750.13079.rterry@pacific.net.au (view raw or flat)
Thread:
Lists: pgsql-novice
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



Responses

pgsql-novice by date

Next:From: Mike EllsworthDate: 2009-01-27 23:04:32
Subject: Re: using to_number() in a select query with ranges
Previous:From: postgres-noviceDate: 2009-01-27 20:47:55
Subject: Re: Array of C integers to temporary table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group