|
 |
asp_databases thread: Trouble with SQL statement
Message #1 by chrscote@9... on Wed, 24 Oct 2001 02:08:46
|
|
I am trying to create an INNER JOIN statement joining a table of names
with a table of binary data. I would like to have an SQL statement that
will join the table "Names" with the field "TF" in a "Data" table, but I
only want a count of those times when this field is set to True. I am
using an Access database if that makes any difference.
Chris
Message #2 by David Cameron <dcameron@i...> on Wed, 24 Oct 2001 15:57:42 +1000
|
|
Do you mean bit/boolean or binary? From the second half of your email it
seems more like you mean bit/boolean. Try something like this:
SELECT N.txtName, COUNT(D.DataID) AS IDCount
FROM tblName N
INNER JOIN tblData D ON
D.NameID = N.NameID
WHERE D.blnField = True
GROUP BY N.txtName
Alter as fits your needs.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: chrscote@9... [mailto:chrscote@9...]
Sent: Wednesday, 24 October 2001 12:09 PM
To: ASP Databases
Subject: [asp_databases] Trouble with SQL statement
I am trying to create an INNER JOIN statement joining a table of names
with a table of binary data. I would like to have an SQL statement that
will join the table "Names" with the field "TF" in a "Data" table, but I
only want a count of those times when this field is set to True. I am
using an Access database if that makes any difference.
Chris
Message #3 by chrscote@9... on Thu, 25 Oct 2001 01:23:20
|
|
Thank you Dave, that helped a lot. The problem is that, though I thought
just getting the answer for a single instance would work, I have 2
different boolean/binary fields, but I can't figure out how to make this
work. I'd like to count the number of times the field1 is true and then
separately, the number of times that field2 is true. If I do:
SELECT Table1.FldName, COUNT(Table1.bField1) AS bCount1, COUNT
(Table1.bField2) AS bCount2
FROM Table1 INNER JOIN Names ON Table1.Name = Names.Name
WHERE bField1 = True and bField2=True
I end up with wrong data. Sorry I didn't specify this earlier, but as I
said, I figured it would work if I knew how to do a single one.
Chris
>
> Do you mean bit/boolean or binary? From the second half of your email it
> seems more like you mean bit/boolean. Try something like this:
>
> SELECT N.txtName, COUNT(D.DataID) AS IDCount
> FROM tblName N
> INNER JOIN tblData D ON
> D.NameID = N.NameID
> WHERE D.blnField = True
> GROUP BY N.txtName
>
> Alter as fits your needs.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
Message #4 by "Ken Schaefer" <ken@a...> on Thu, 25 Oct 2001 11:22:36 +1000
|
|
I'm not sure you can do what you want...
You could try using GROUP BY, if you want to separate out the counts of a
particular field.
That said, if you want a list of Table1.FldName (eg there are 20 of these in
the table), plus a single value for the number of times that bCount1 is
True, plus a single value for the number of times that bCount2 is true,
you're outta luck, unless you want to write a really inefficient query.
Remember that you recordset comes back as a NxM table...
FldName Count1 Count2
a 5 6
b ?? ??
c
d
e
what goes where the ?? are?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <chrscote@9...>
Subject: [asp_databases] RE: Trouble with SQL statement
: Thank you Dave, that helped a lot. The problem is that, though I thought
: just getting the answer for a single instance would work, I have 2
: different boolean/binary fields, but I can't figure out how to make this
: work. I'd like to count the number of times the field1 is true and then
: separately, the number of times that field2 is true. If I do:
:
: SELECT Table1.FldName, COUNT(Table1.bField1) AS bCount1, COUNT
: (Table1.bField2) AS bCount2
: FROM Table1 INNER JOIN Names ON Table1.Name = Names.Name
: WHERE bField1 = True and bField2=True
:
: I end up with wrong data. Sorry I didn't specify this earlier, but as I
: said, I figured it would work if I knew how to do a single one.
:
: Chris
:
:
: >
: > Do you mean bit/boolean or binary? From the second half of your email it
: > seems more like you mean bit/boolean. Try something like this:
: >
: > SELECT N.txtName, COUNT(D.DataID) AS IDCount
: > FROM tblName N
: > INNER JOIN tblData D ON
: > D.NameID = N.NameID
: > WHERE D.blnField = True
: > GROUP BY N.txtName
: >
: > Alter as fits your needs.
: >
: > regards
: > David Cameron
: > nOw.b2b
: > dcameron@i...
:
:
$subst('Email.Unsub')
Message #5 by David Cameron <dcameron@i...> on Thu, 25 Oct 2001 11:23:44 +1000
|
|
The problem is that you are effectively grouping by a two different
criteria. Generally one SELECT statement can only have one COUNT, unless for
some unfathomable reason you want to return the same data in two fields.
For this you need to run to a correlated subquery for your second COUNT.
From what I could see of the documentation for Access correlated subqueries
are supported.
SELECT Table1.FldName, COUNT(Table1.bField1) AS bCount1,
(SELECT COUNT(T1.bField1)
FROM Table1 T1
WHERE T1.ID = Table1.ID) bCount2
FROM Table1
INNER JOIN Names ON
Table1.Name = Names.Name
WHERE bField1 = True
This is where Table1.ID is the Identity for the table.
regards
David Cameron
nOw.b2b
dcameron@i...
-----Original Message-----
From: chrscote@9... [mailto:chrscote@9...]
Sent: Thursday, 25 October 2001 11:23 AM
To: ASP Databases
Subject: [asp_databases] RE: Trouble with SQL statement
Thank you Dave, that helped a lot. The problem is that, though I thought
just getting the answer for a single instance would work, I have 2
different boolean/binary fields, but I can't figure out how to make this
work. I'd like to count the number of times the field1 is true and then
separately, the number of times that field2 is true. If I do:
SELECT Table1.FldName, COUNT(Table1.bField1) AS bCount1, COUNT
(Table1.bField2) AS bCount2
FROM Table1 INNER JOIN Names ON Table1.Name = Names.Name
WHERE bField1 = True and bField2=True
I end up with wrong data. Sorry I didn't specify this earlier, but as I
said, I figured it would work if I knew how to do a single one.
Chris
>
> Do you mean bit/boolean or binary? From the second half of your email it
> seems more like you mean bit/boolean. Try something like this:
>
> SELECT N.txtName, COUNT(D.DataID) AS IDCount
> FROM tblName N
> INNER JOIN tblData D ON
> D.NameID = N.NameID
> WHERE D.blnField = True
> GROUP BY N.txtName
>
> Alter as fits your needs.
>
> regards
> David Cameron
> nOw.b2b
> dcameron@i...
Message #6 by "Dallas Martin" <dmartin@z...> on Thu, 25 Oct 2001 00:29:04 -0400
|
|
Look at your logic. If you are selecting only those rows where bField1= true
and bField2=true,
then what are you counting? Both counts() will be the same!!!
Your select statement is effectively equivalent to this statement:
SELECT Table1.FldName, COUNT(*) AS bCount
FROM Table1
INNER JOIN Names ON Table1.Name = Names.Name
WHERE bField1 = True and bField2=True
GROUP BY Table1.FldName
If you are using SQL SERVER, then two SEARCHED CASE statements would
probably
give you the results you want:
SELECT Table1.FldName,
SUM(CASE bField1 WHEN true THEN 1 ELSE NULL END) AS bCount1,
SUM(CASE bField2 WHEN true THEN 1 ELSE NULL END) AS bCount2,
FROM Table1
INNER JOIN Names ON Table1.Name = Names.Name
GROUP BY Table1.FldName
For Access, this should work ,
SELECT Table1.FldName,
SUM(iif(bField1 = true,1,null))AS bCount1,
SUM(iif(bField2 = true,1,null))AS bCount2,
FROM Table1
INNER JOIN Names ON Table1.Name = Names.Name
GROUP BY Table1.FldName
Remember SUM() will ignore NULLS, COUNT() doesn't unless you specify
COUNT(ALL ) or COUNT(DISTINCT )
Dallas
----- Original Message -----
From: <chrscote@9...>
To: "ASP Databases" <asp_databases@p...>
Sent: Thursday, October 25, 2001 1:23 AM
Subject: [asp_databases] RE: Trouble with SQL statement
> Thank you Dave, that helped a lot. The problem is that, though I thought
> just getting the answer for a single instance would work, I have 2
> different boolean/binary fields, but I can't figure out how to make this
> work. I'd like to count the number of times the field1 is true and then
> separately, the number of times that field2 is true. If I do:
>
> SELECT Table1.FldName, COUNT(Table1.bField1) AS bCount1, COUNT
> (Table1.bField2) AS bCount2
> FROM Table1 INNER JOIN Names ON Table1.Name = Names.Name
> WHERE bField1 = True and bField2=True
>
> I end up with wrong data. Sorry I didn't specify this earlier, but as I
> said, I figured it would work if I knew how to do a single one.
>
> Chris
>
>
> >
> > Do you mean bit/boolean or binary? From the second half of your email it
> > seems more like you mean bit/boolean. Try something like this:
> >
> > SELECT N.txtName, COUNT(D.DataID) AS IDCount
> > FROM tblName N
> > INNER JOIN tblData D ON
> > D.NameID = N.NameID
> > WHERE D.blnField = True
> > GROUP BY N.txtName
> >
> > Alter as fits your needs.
> >
> > regards
> > David Cameron
> > nOw.b2b
> > dcameron@i...
>
>
>
|
|
|
View Cart
