pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedResults per letter query
7+ messages / 2 participants
[nested] [flat]
* Results per letter query
@ 2007-06-21 09:10 Dani Castaños <dcastanos@androme.es>
2007-06-21 10:04 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dani Castaños @ 2007-06-21 09:10 UTC (permalink / raw)
To: pgsql-sql
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??
Thank you in advance!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Results per letter query
2007-06-21 09:10 Results per letter query Dani Castaños <dcastanos@androme.es>
@ 2007-06-21 10:04 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:42 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
0 siblings, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2007-06-21 10:04 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Results per letter query
2007-06-21 09:10 Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:04 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-06-21 10:42 ` Dani Castaños <dcastanos@androme.es>
2007-06-21 10:50 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dani Castaños @ 2007-06-21 10:42 UTC (permalink / raw)
To: pgsql-sql
>> 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)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Results per letter query
2007-06-21 09:10 Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:04 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:42 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
@ 2007-06-21 10:50 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:59 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
0 siblings, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2007-06-21 10:50 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 21.06.2007, um 12:42:52 +0200 mailte Dani Castaños folgendes:
> >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:
change the generate_series(65,90) to generate_series(32,90)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Results per letter query
2007-06-21 09:10 Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:04 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:42 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:50 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-06-21 10:59 ` Dani Castaños <dcastanos@androme.es>
2007-06-21 11:12 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
0 siblings, 1 reply; 7+ messages in thread
From: Dani Castaños @ 2007-06-21 10:59 UTC (permalink / raw)
To: pgsql-sql
> am Thu, dem 21.06.2007, um 12:42:52 +0200 mailte Dani Castaños folgendes:
>
>>> 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:
>>
>
> change the generate_series(65,90) to generate_series(32,90)
>
>
> Andreas
>
With only changing 65 to 32:
ERROR: invalid regular expression: parentheses () not balanced
I think, it could be a problem with UPPER and non alphabetical chars
--
*Dani Castaños Sánchez*
dcastanos@androme.es <mailto:dcastanos@androme.es>
ANDROME Iberica
http://www.androme.es
Constança, 5, 08029 Barcelona
Tel: +34 934948850
Fax: +34 934196094
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Results per letter query
2007-06-21 09:10 Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:04 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:42 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:50 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:59 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
@ 2007-06-21 11:12 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:08 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
0 siblings, 1 reply; 7+ messages in thread
From: A. Kretschmer @ 2007-06-21 11:12 UTC (permalink / raw)
To: pgsql-sql
am Thu, dem 21.06.2007, um 12:59:05 +0200 mailte Dani Castaños folgendes:
> >change the generate_series(65,90) to generate_series(32,90)
> >
> >
> >Andreas
> >
> With only changing 65 to 32:
>
> ERROR: invalid regular expression: parentheses () not balanced
>
> I think, it could be a problem with UPPER and non alphabetical chars
No, the ~ - operator (Regex), try this:
select chr(x), count(1) from generate_series(32,90) x, w where upper(substring (w.t from 1 for 1)) = chr(x) group by 1;
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Results per letter query
2007-06-21 09:10 Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:04 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:42 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:50 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:59 ` Re: Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 11:12 ` Re: Results per letter query A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2007-06-21 13:08 ` Dani Castaños <dcastanos@androme.es>
0 siblings, 0 replies; 7+ messages in thread
From: Dani Castaños @ 2007-06-21 13:08 UTC (permalink / raw)
To: pgsql-sql
A. Kretschmer escribió:
> am Thu, dem 21.06.2007, um 12:59:05 +0200 mailte Dani Castaños folgendes:
>
>>> change the generate_series(65,90) to generate_series(32,90)
>>>
>>>
>>> Andreas
>>>
>>>
>> With only changing 65 to 32:
>>
>> ERROR: invalid regular expression: parentheses () not balanced
>>
>> I think, it could be a problem with UPPER and non alphabetical chars
>>
>
> No, the ~ - operator (Regex), try this:
>
> select chr(x), count(1) from generate_series(32,90) x, w where upper(substring (w.t from 1 for 1)) = chr(x) group by 1;
>
>
> Andreas
>
Thanks Andreas!
I thik i've found a better solution for my problem:
I got another solution, but I've used EXPLAIN ANALYZE, and yours is better:
EXPLAIN ANALYZE select chr(x), count(1) from generate_series(32,90) x,
sip_customer_services where upper(substring
(sip_customer_services.service_name from 1 for 1)) = chr(x) group by 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=37.00..39.25 rows=150 width=4) (actual
time=1.652..1.716 rows=13 loops=1)
-> Hash Join (cost=9.38..36.25 rows=150 width=4) (actual
time=0.979..1.490 rows=18 loops=1)
Hash Cond: (chr("outer".x) =
upper("substring"(("inner".service_name)::text, 1, 1)))
-> Function Scan on generate_series x (cost=0.00..12.50
rows=1000 width=4) (actual time=0.114..0.332 rows=59 loops=1)
-> Hash (cost=9.30..9.30 rows=30 width=10) (actual
time=0.647..0.647 rows=18 loops=1)
-> Seq Scan on sip_customer_services (cost=0.00..9.30
rows=30 width=10) (actual time=0.295..0.442 rows=18 loops=1)
Total runtime: 2.147 ms
(7 rows)
test=# EXPLAIN ANALYZE select count(*), upper(substr(service_name, 1,
1)) from sip_customer_services group by upper(substr(service_name, 1,1));
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=9.60..10.13 rows=30 width=10) (actual
time=0.704..0.766 rows=13 loops=1)
-> Seq Scan on sip_customer_services (cost=0.00..9.45 rows=30
width=10) (actual time=0.332..0.530 rows=18 loops=1)
Total runtime: 1.065 ms
(3 rows);
Thank you very much, anyway!
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2007-06-21 13:08 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-21 09:10 Results per letter query Dani Castaños <dcastanos@androme.es>
2007-06-21 10:04 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:42 ` Dani Castaños <dcastanos@androme.es>
2007-06-21 10:50 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 10:59 ` Dani Castaños <dcastanos@androme.es>
2007-06-21 11:12 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2007-06-21 13:08 ` Dani Castaños <dcastanos@androme.es>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox