By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,830 Members | 658 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,830 IT Pros & Developers. It's quick & easy.

SQL - counting various tables and various records in one query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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


Nov 12 '05 #2

P: n/a
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)

Nov 12 '05 #3

P: n/a
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)


Nov 12 '05 #4

P: n/a
MGFoster <me@privacy.com> 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)



Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.