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 4 4681
Chad Reid <ch******@gaebe l.ca> wrote in
news:Pine.WNT.4 .58.03092619203 00.-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,trueva lue,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
Bob Quintal wrote: Chad Reid <ch******@gaebe l.ca> wrote in news:Pine.WNT.4 .58.03092619203 00.-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,trueva lue,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)
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)
MGFoster <me@privacy.com > wrote in
news:5u******** *********@newsr ead3.news.pas.e arthlink.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)
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Brenda J. |
last post by:
Hello all
I'm sure this is a relatively simple query to create, but I can't seem
to get my head around it.
Here is an example of the two tables I am using:
tblAccountTypes
AccountTypeID AccountType AccountNumberRange
|
by: Megan |
last post by:
hi everybody-
i'm having a counting problem i hope you guys and gals could give me
some help with. i have a query that retrieves a bevy of information
from several different tables.
first let me give you a little background. this database is kind of
like a human resources database. it collects info about people, where
they work, and if they have any work-related issues where they work.
|
by: svdh |
last post by:
I have posed a question last saturday and have advanced alot in the
meantime. But I am still not there
Problem is that I try to merging various fields from various tables in
one document in Word
1. Query..I want to keep the fields seperatred. I do not want to sent
on field with all accumulated languages from one person to Word. Each
language should appear in the document in a separate cell
Cross tables are not delivering the result I...
|
by: ChadDiesel |
last post by:
I appreciate the help on this group. I know I've posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other employee knows anything about
Access. I've searched Google Groups, and that has been a lot of help, but
there are some questions that I just can't find the answer to. I'll try to
take it easy on the group after this question.
I have one more...
|
by: mattytee123 |
last post by:
I have about 20 tables, of which I would like to do a union query and
count of how many of each different code there is?
The simplified verson of the table is structured like this.
Code Count
1234 1
2468 1
1234 1
2468 1
| |
by: RussCRM |
last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for statistical purposes.
I've been using
Here’s the situation:
I have two main tables:
Guest (stores data such as GuestID, First Name, Last Name, etc.) and
Services (stores data such as the type of service the guest used (Shelter Bed, Lunch, Dinner,...
|
by: mwang |
last post by:
I have sql like this:
select count(*) from TRANSACTION transaction0_ where transaction0_.CREATED > sysdate - 10 and exists (select lineItem.TRANSACTION_ID from LINEITEM lineItem where lineItem.EXTERNAL_ID like 'ext%' and lineItem.TRANSACTION_ID = transaction0_.TRANSACTION_ID)
The query takes more than 1 minute to complete.
One tricky thing is if I use select * instead of select count(*), then it completes within 1 second.
I wonder if...
|
by: crassostrea |
last post by:
Hello and Happy New Year,
I have two tables in Access 2003 (Windows XP) with similar, but different, information. We’ll call them table A and table B. I want to count the number of records in each table and display how many of each there are, and the total, by year:
2008 A B A+B
2007 A B A+B
2006 A B A+B
Right now I have a counting query for each of the tables, separately, but I can’t figure out how to properly join...
|
by: jasone |
last post by:
hey, this is what ive got so far:
("SELECT (Select count(*) from tbl_flight_details) + (select count(*) FROM tbl_flight_departures) as grandtotal")
i need to count all the records in the above 2 tables, following this query i need to count all the records depending on a certaion value... at the moment ive got them counting from just one table which is fine.
rs6=cn.execute ("Select count(*) as cardiffdeparts from...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |