I want to add the COUNT results of two statements. The problem I'm having is the WHERE clause used for each COUNT uses the same field, but has different criteria. How would I add the COUNTs based on different criteria?

SELECT COUNT(DISTINCT(mxssn))
FROM mx
WHERE mxtots > 0 AND mxssn <> '999-99-9999'

SELECT COUNT(member)
FROM mx
WHERE mxssn = '666-66-6666'

You said "I want to Add"

 A) Are you inserting 2 different resluts into another table?
 B) Have you tried to UNION these 2 scripts?
 C) Or am I completely missing the picture?

Regards,
Daniel

Here's one way of doing it:

SELECT A.DistinctCount + B.MemberCount
FROM
(SELECT COUNT(DISTINCT(mxssn)) AS DistinctCount
FROM mx
WHERE mxtots > 0 AND mxssn <> '999-99-9999') A,
(SELECT COUNT(member) AS MemberCount
FROM mx
WHERE mxssn = '666-66-6666') B

Here's another way of doing it:

SELECT SUM(RecordCount)
FROM (
SELECT COUNT(DISTINCT(mxssn)) AS RecordCount
FROM mx
WHERE mxtots > 0 AND mxssn <> '999-99-9999'
UNION ALL
SELECT COUNT(member) AS RecordCount
FROM mx
WHERE mxssn = '666-66-6666') A

SQL Server Helper
How well do you know SQL? Find out with the free test assessment from SQL Server Helper!!!
http://www.sql-server-helper.com/free-test/default.aspx

Got a SQL Server Question? Ask us here: http://www.sql-server-helper.com/forums/default.asp

I want to put a single total of the two into another table. sshelper's statement does what I need.

Thanks!