combine these two sql statements

SELECT bms_id,email_address,COUNT(*)
INTO #temp
FROM emp_db
WHERE email_address IS NOT NULL
GROUP BY bms_id,email_address
ORDER BY bms_id DESC,COUNT(*) DESC

SELECT bms_id COUNT(*)
FROM #TEMP
GROUP BY bms_id
ORDER BY COUNT(*) DESC

How can i put these two statements into a single sql statement.

Thanks.in the FROM part of the code, you could embed the second select statement.

maybe use a stored procedure or view as the source for a second TABLE IN THE FROM PART|||What about derived table? I did not test this query but try this idea...

SELECT q.bms_id, COUNT(*)
(SELECT bms_id,email_address,COUNT(*) as f1
FROM emp_db
WHERE email_address IS NOT NULL
GROUP BY bms_id,email_address) as q
GROUP BY q.bms_id
ORDER BY COUNT(*) DESC

From BOL:

USE pubs
SELECT RTRIM(a.au_fname) + ' ' + LTRIM(a.au_lname) AS Name, d1.title_id
FROM authors a, (SELECT title_id, au_id FROM titleauthor) AS d1
WHERE a.au_id = d1.au_id
ORDER BY a.au_lname, a.au_fname|||This will count the number of distinct emails per id. Is this the requirement?

SELECT bms_id, COUNT(DISTINCT Email)
FROM emp
WHERE Email IS NOT NULL
GROUP BY bms_id
ORDER BY 2 DESC