SQL stored procedure: how do you use the returned value in the same query?

Question

How do I use the value returned by a query in the same procedure? (sorry for noob-ism)

I'm trying to get a number of users with an initial beginning with whatever as a list - i.e. A(4) B(2) c(5) etc

  SELECT DISTINCT LEFT(last_name, 1) AS initial 
    FROM users 
ORDER BY initial

How do I then go on to ask:

select COUNT(*) as NumTeam 
  from users 
 where last_name like initial + '%'

Answer 1

use a group by query:

select left(last_name, 1) as initial, count(*) as initial_count
  from users
  group by left(last_name, 1)
  order by left(last_name, 1)

Answer 2

You can use inital in the GROUP BY clause:

SELECT DISTINCT LEFT(last_name, 1) AS initial, count(1) AS NumTeam 
FROM users
GROUP BY initial ORDER BY initial

Answer 3

This will definitely work on SQL Server 2005 (which I tested it on):

DECLARE @People TABLE (
    [id] [int] IDENTITY(1,1) NOT NULL,
    [fname] [nvarchar](50) NOT NULL,
    [lname] [nvarchar](50) NOT NULL
)

INSERT INTO @People([fname], [lname])
SELECT 'Joseph', 'Adama' UNION ALL
SELECT 'Adam', 'Joseph' UNION ALL
SELECT 'Bryan', 'Adams' UNION ALL
SELECT 'Charlie', 'Brown' UNION ALL
SELECT 'Charles', 'Babbage' UNION ALL
SELECT 'Charles', 'Schultz'

SELECT        LEFT(lname, 1) [Letter], COUNT(id) [Count]
FROM          @People
GROUP BY      LEFT(lname, 1)
ORDER BY      LEFT(lname, 1)

It yields the result set:

Letter   Count
A        2
B        2
J        1
S        1   

Answer 4

to answer the question proper:

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 | 
+------+------+------+--------------------+

this comes from http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

To answer your question specifically, I believe you want something like this:

    SELECT LEFT(last_name, 1) AS initial,COUNT(*) as NumTeam FROM users GROUP BY LEFT(last_name, 1)

If you want the initials alphabetically:

SELECT * FROM(
 SELECT LEFT(last_name, 1) AS initial,COUNT(*) as NumTeam FROM users GROUP BY LEFT(last_name, 1)
) as initial_counts order by initial;