SQL - counting various tables and various records in one query

[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.

---
Chad Reid
Strategic Tech Solutions
Email: ch******@gaebel.ca
ICQ: 2319732

[Bob Quintal]

Chad Reid <ch******@gaebel.ca> wrote in
news:Pine.WNT.4.58.0309261920300.-895817@tesko:

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.

Instead of counting, you should be summing.

SELECT
Sum(iif(mID=1,1,0) as numMarried,
Sum(iif(mID=2,1,0) as numSingle,
Sum(iif(mID=3,1,0) as numDivorced,
Sum(iif(mID=4,1,0) as numWidowed,
from tblClients

Theory: the iif(test,truevalue,falsevalue) function will test your
mID=x and return the truevalue or falsevalue based on whether the
test is true or false.

You can google up the thread on counting boolean fields, and
simplify the equations even more.

e.g. Sum(abs(mID=1)) will be about four times faster, important if
you have a hundred thousand clients.

Bob Q.

---
Chad Reid
Strategic Tech Solutions
Email: ch******@gaebel.ca
ICQ: 2319732

[MGFoster]

Bob Quintal wrote:

Chad Reid <ch******@gaebel.ca> wrote in
news:Pine.WNT.4.58.0309261920300.-895817@tesko:

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.

Instead of counting, you should be summing.

SELECT
Sum(iif(mID=1,1,0) as numMarried,
Sum(iif(mID=2,1,0) as numSingle,
Sum(iif(mID=3,1,0) as numDivorced,
Sum(iif(mID=4,1,0) as numWidowed,
from tblClients

Theory: the iif(test,truevalue,falsevalue) function will test your
mID=x and return the truevalue or falsevalue based on whether the
test is true or false.

You can google up the thread on counting boolean fields, and
simplify the equations even more.

e.g. Sum(abs(mID=1)) will be about four times faster, important if
you have a hundred thousand clients.

Bob Q.

---
Chad Reid
Strategic Tech Solutions
Email: ch******@gaebel.ca
ICQ: 2319732

Ideally, you would have a lookup table that has a string description of
the mID code. E.g.:

Lookup table "RelationTypes":

mID Description
--- -----------
1 Married
2 Single
3 Divorced
4 Widowed

Then you could create a query like this:

SELECT R.Description, Count(*) As NumberOfPersons
FROM tblClients AS C INNER JOIN RelationTypes AS R
ON C.mID = R.mID
ORDER BY R.Description

Then whenever you added a new Description to the RelationTypes table you
would not have to change your query to include that new Description.

MGFoster:::mgf
Oakland, CA (USA)

[MGFoster]

I forgot to put in the GROUP BY clause:

SELECT R.Description, Count(*) As NumberOfPersons
FROM tblClients AS C INNER JOIN RelationTypes AS R
ON C.mID = R.mID
GROUP BY R.Description
ORDER BY R.Description

MGFoster:::mgf
Oakland, CA (USA)
MGFoster wrote:

Ideally, you would have a lookup table that has a string description of
the mID code. E.g.:

Lookup table "RelationTypes":

mID Description
--- -----------
1 Married
2 Single
3 Divorced
4 Widowed

Then you could create a query like this:

SELECT R.Description, Count(*) As NumberOfPersons
FROM tblClients AS C INNER JOIN RelationTypes AS R
ON C.mID = R.mID
ORDER BY R.Description

Then whenever you added a new Description to the RelationTypes table you
would not have to change your query to include that new Description.

MGFoster:::mgf
Oakland, CA (USA)

[Bob Quintal]

MGFoster <[e-mail]> wrote in
news:5u*****************@newsread3.news.pas.earthl ink.net:

I forgot to put in the GROUP BY clause:
You also forgot the crosstab query to move your four rows into the
one the OP wanted. ;)

Bob Q.
SELECT R.Description, Count(*) As NumberOfPersons
FROM tblClients AS C INNER JOIN RelationTypes AS R
ON C.mID = R.mID
GROUP BY R.Description
ORDER BY R.Description

MGFoster:::mgf
Oakland, CA (USA)
MGFoster wrote:

Ideally, you would have a lookup table that has a string
description of the mID code. E.g.:

Lookup table "RelationTypes":

mID Description
--- -----------
1 Married
2 Single
3 Divorced
4 Widowed

Then you could create a query like this:

SELECT R.Description, Count(*) As NumberOfPersons
FROM tblClients AS C INNER JOIN RelationTypes AS R
ON C.mID = R.mID
ORDER BY R.Description

Then whenever you added a new Description to the
RelationTypes table you would not have to change your query
to include that new Description.

MGFoster:::mgf
Oakland, CA (USA)