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)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
