By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,263 Members | 2,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,263 IT Pros & Developers. It's quick & easy.

Creating an A to Z list from query

P: n/a
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

Feb 9 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<de**@chronofish.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.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.
Feb 9 '06 #2

P: n/a
de**@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
Feb 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.