Message-ID: <467A562C.2020504@androme.es>
Date: Thu, 21 Jun 2007 12:42:52 +0200
From: =?ISO-8859-1?Q?Dani_Casta=F1os?= <dcastanos@androme.es>
User-Agent: Thunderbird 2.0.0.4 (Windows/20070604)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Results per letter query
References: <467A406A.3060907@androme.es>
	<20070621100425.GH1405@a-kretschmer.de>
In-Reply-To: <20070621100425.GH1405@a-kretschmer.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


>> Hi!
>>
>> I'm trying to build a query to get if there is an occurrence for a field 
>> for each alphabetical letter.
>> My first thought to know it was to do something like:
>>
>> SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'A%' ) 
>> LIMIT 1;
>> SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'B%' ) 
>> LIMIT 1;
>> SELECT COUNT(field) FROM table WHERE UPPER( field )  LIKE UPPER( 'C%' ) 
>> LIMIT 1;
>> ...
>> and so on...
>>
>> Is there any way to do it in only one query??
>>     
>
> I'm not sure if i understand you correctly, sorry, if not.
>
> test=*# select * from w;
>    t
> --------
>  test
>  foo
>  bar
>  foobar
> (4 rows)
>
> test=*# select chr(x), count(1) from generate_series(65,90) x, w where
> upper(substring (w.t from 1 for 1)) ~ chr(x) group by 1;
>  chr | count
> -----+-------
>  T   |     1
>  B   |     1
>  F   |     2
> (3 rows)
>
>
>
> Andreas
>   
It's exactly what i want. Just one more thing... What if i want also the 
ones that begin by a non-alphabetical character.
In your example:

test=*# select * from w;
   t
--------
 test
 foo
 bar
 foobar
 1foobar
 /ertw
 @weras


and have:
chr | count
-----+-------
 T   |     1
 B   |     1
 F   |     2
 _   |     3
(4 rows)