Creating an A to Z list from query

Creating an A to Z list from query

am 09.02.2006 15:56:57 von ChronoFish

Hello,

I am creating an a to z list - basically a count of all results that
start with the letter "A", "B", "C" .... and so on.

I am pretty poor at SQL so I am sure some brains out there can do
better than I have here. What I have is working, I just want to make
sure that it is optomized.


So let's assume I have some query "$query" that I want to run and get
an A..Z list based on column "$column".

Let's further assume that '$query" produces the following results, and
that $column is equal to "last_name".

last_name
---------------
Anderson
Bitmore
brown
Bogus

My AZlist query would look like this:

select * from
(SELECT count(alist.$column) as a from ($query) as alist where
alist.$column like 'a%' or alist.$column like 'A%' ) as a_result,
(SELECT count(blist.$column) as b from ($query) as blist where
blist.$column like 'b%' or blist.$column like 'B%' ) as b_result,
....
(SELECT count(zlist.$column) as z from ($query) as zlist where
zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result;


And this retuns the following result:
a | b |...| z
--------------
1 | 3 |...| 0

Meaning that $query has 1 result where the first letter in $column is
"A" or "a", 3 results where the first letter is "B" or "b" and 0
results where the first letter is "Z" or "z".

What I am afraid of here is that "$query" is being executed 26 times
(once for each letter of the alphabet) . Is there a way to refine
this, or is MySQL (4.x and 5.x) smart enough to optomize this on its
own?

Thanks!
CF

Re: Creating an A to Z list from query

am 09.02.2006 19:53:28 von Bill Karwin

wrote in message
news:1139497017.065291.143070@g47g2000cwa.googlegroups.com.. .
> Hello,
>
> I am creating an a to z list - basically a count of all results that
> start with the letter "A", "B", "C" .... and so on.

I would do this by outputting rows, instead of columns:

SELECT SUBSTRING(last_name, 1, 1) as last_name_initial, COUNT(*)
FROM ($query)
GROUP BY last_name_initial

Regards,
Bill K.

Re: Creating an A to Z list from query

am 09.02.2006 20:21:28 von Jonathan

deja@chronofish.com wrote:
> Hello,
>
> I am creating an a to z list - basically a count of all results that
> start with the letter "A", "B", "C" .... and so on.
>
> I am pretty poor at SQL so I am sure some brains out there can do
> better than I have here. What I have is working, I just want to make
> sure that it is optomized.
>
>
> So let's assume I have some query "$query" that I want to run and get
> an A..Z list based on column "$column".
>
> Let's further assume that '$query" produces the following results, and
> that $column is equal to "last_name".
>
> last_name
> ---------------
> Anderson
> Bitmore
> brown
> Bogus
>
> My AZlist query would look like this:
>
> select * from
> (SELECT count(alist.$column) as a from ($query) as alist where
> alist.$column like 'a%' or alist.$column like 'A%' ) as a_result,
> (SELECT count(blist.$column) as b from ($query) as blist where
> blist.$column like 'b%' or blist.$column like 'B%' ) as b_result,
> ...
> (SELECT count(zlist.$column) as z from ($query) as zlist where
> zlist.$column like 'z%' or zlist.$column like 'Z%' ) as z_result;
>
>
> And this retuns the following result:
> a | b |...| z
> --------------
> 1 | 3 |...| 0
>
> Meaning that $query has 1 result where the first letter in $column is
> "A" or "a", 3 results where the first letter is "B" or "b" and 0
> results where the first letter is "Z" or "z".
>
> What I am afraid of here is that "$query" is being executed 26 times
> (once for each letter of the alphabet) . Is there a way to refine
> this, or is MySQL (4.x and 5.x) smart enough to optomize this on its
> own?
>
> Thanks!
> CF
>

Not tested but I think something like this should do the trick (unless
you also need the now which letters appear zero times.

SELECT left(table.field, 1) as Field1, count(left(table.field, 1)) from
user GROUP BY Field1;

Jonathan