am Thu, dem 21.06.2007, um 11:10:02 +0200 mailte Dani Castaños folgendes: > 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 -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
