SQL - counting various records in various tables at once

[Chad Reid]

Hello,

I have a bit of code that obviously doesn't work, but I need
help creating a query that would have the same effect as if this
query was working.

SELECT * FROM
(SELECT Count(*) AS numMarried FROM tblClients WHERE mID = 1),
(SELECT Count(*) AS numSingle FROM tblClients WHERE mID = 2),
(SELECT Count(*) AS numDivorced FROM tblClients WHERE mID = 3),
(SELECT Count(*) AS numWidowed FROM tblClients WHERE mID = 4);

Right now I have broken each count into a separate query, and
then SELECT * from all of the count queries, but there has to
be a better way.

 

[Douglas J. Steele]

Do you have a table that identifies that mID 1 is "Married", mID 2 is
"Single" and so on?

Assuming it's called tblMaritalStatus, try:

SELECT tblMaritalStatus.Status, Count(*)
FROM tblClients
INNER JOIN tblMaritalStatus
ON tblClients.mID = tblMaritalStatus.mID
GROUP BY tblMaritalStatus.Status

For what it's worth, to do it your way, you'd need to UNION together the
various queries:

SELECT "Married" AS Status, Count(*) FROM tblClients WHERE mID = 1
UNION
SELECT "Single", Count(*) FROM tblClients WHERE mID = 2
UNION
SELECT "Divorced", Count(*) FROM tblClients WHERE mID = 3
UNION
SELECT "Widowed", Count(*) FROM tblClients WHERE mID = 4