Group count query advise

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have a field that can have either A, B or C as its value. I need to write
a query that will return the number of A's, B's & C's in the field X for a
given criteria and then the total records for the same criteria. How do I
write such as query?

Thanks

Regards
 
John said:
Hi

I have a field that can have either A, B or C as its value. I need to
write a query that will return the number of A's, B's & C's in the
field X for a given criteria and then the total records for the same
criteria. How do I write such as query?
Click to expand...

Here's example SQL (untested):

SELECT
Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,
Count(*) As TotalCount
FROM [TheTable]
WHERE SomeField = SomeCriterion;
 
Thanks.

Dumb question: Why do we need the abs function?

Regards

Dirk Goldgar said:
John said:
Hi

I have a field that can have either A, B or C as its value. I need to
write a query that will return the number of A's, B's & C's in the
field X for a given criteria and then the total records for the same
criteria. How do I write such as query?
Click to expand...

Here's example SQL (untested):

SELECT
Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,
Count(*) As TotalCount
FROM [TheTable]
WHERE SomeField = SomeCriterion;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
John said:
Thanks.

Dumb question: Why do we need the abs function?

Regards

Dirk Goldgar said:
SELECT
Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,
Count(*) As TotalCount
FROM [TheTable]
WHERE SomeField = SomeCriterion;
Click to expand...
Click to expand...

Each of the logical expressions [X]='A', [X]='B', and [X]='C' evaluates
to a value of True or False. In VB and Jet, logical True is represented
by the numeric value -1, and False by 0. Each call to the Sum()
function adds up the True values for the records that match the WHERE
condition, yielding a negative number; that is, -1 times the number of
records for which the logical expression was true. We want to return a
positive number of True values, so we use the Abs() function to take the
absolute value of the negative number returned by Sum(). That gives us
the positive number we want.
 
The logical expression [X] = 'A' returns 0 if the expression is FALSE,
but -1 if it is TRUE. Your sum will thus be the *negative* of the
number of records where the expression is TRUE. Abs() converts the
negative to a positive.

If the query were run in SQL/Server instead of Access, True would be
represented by +1 instead of -1, but Abs will just return the positive
so it will still work.


John W. Vinson[MVP]
Thanks.

Dumb question: Why do we need the abs function?

Regards

Dirk Goldgar said:
John said:
Hi

I have a field that can have either A, B or C as its value. I need to
write a query that will return the number of A's, B's & C's in the
field X for a given criteria and then the total records for the same
criteria. How do I write such as query?
Click to expand...

Here's example SQL (untested):

SELECT
Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,
Count(*) As TotalCount
FROM [TheTable]
WHERE SomeField = SomeCriterion;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Thanks for this gem. Pure poetry.

Regards

Dirk Goldgar said:
John said:
Thanks.

Dumb question: Why do we need the abs function?

Regards

Dirk Goldgar said:
SELECT
Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,
Count(*) As TotalCount
FROM [TheTable]
WHERE SomeField = SomeCriterion;
Click to expand...
Click to expand...

Each of the logical expressions [X]='A', [X]='B', and [X]='C' evaluates
to a value of True or False. In VB and Jet, logical True is represented
by the numeric value -1, and False by 0. Each call to the Sum()
function adds up the True values for the records that match the WHERE
condition, yielding a negative number; that is, -1 times the number of
records for which the logical expression was true. We want to return a
positive number of True values, so we use the Abs() function to take the
absolute value of the negative number returned by Sum(). That gives us
the positive number we want.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Hi,


can also try a crosstab like:


TRANSFORM Count(*) as theValue
SELECT SUM(theValue)
FROM myTable
PIVOT fieldNameWithABCvalues


Vanderghast, Access MVP
 
I'm a bit confused as to why you used the Sum function instead of the Count
function in the first part. I would think that a Sum can only be performed
on a field that contains a number not a letter as I understood the original
question. Am I incorrect in my assumption? I have a very similar problem in
that I have a Gender field that contains an "m" or "f" and I need to know how
many M's there are, and separately, how many F's there are. I figure I'd put
the answers in an alias called Males and Females respectively. This seems
like basically the same problem but I guess I'm not sure how the

Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,

parts work.


--
Sometimes I wonder if men and women really suit each other. Perhaps they
should live next door and just visit now and then.


Dirk Goldgar said:
John said:
Hi

I have a field that can have either A, B or C as its value. I need to
write a query that will return the number of A's, B's & C's in the
field X for a given criteria and then the total records for the same
criteria. How do I write such as query?
Click to expand...

Here's example SQL (untested):

SELECT
Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,
Count(*) As TotalCount
FROM [TheTable]
WHERE SomeField = SomeCriterion;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
[X]='A' will return -1 (True) if the value of X is currently A, and 0 if it
isn't.

Sum([X]='A') will sum all of those -1 and 0 values.

Abs(Sum([X]='A')) will convert the sum of -1 values to a positive number.
That value represents how many of the [X] values were equal to 'A'

(note that Dirk inadvertently left out the final closing parenthesis in the
original expression)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


AMessyGuy said:
I'm a bit confused as to why you used the Sum function instead of the Count
function in the first part. I would think that a Sum can only be performed
on a field that contains a number not a letter as I understood the original
question. Am I incorrect in my assumption? I have a very similar problem in
that I have a Gender field that contains an "m" or "f" and I need to know how
many M's there are, and separately, how many F's there are. I figure I'd put
the answers in an alias called Males and Females respectively. This seems
like basically the same problem but I guess I'm not sure how the

Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,

parts work.


--
Sometimes I wonder if men and women really suit each other. Perhaps they
should live next door and just visit now and then.


Dirk Goldgar said:
John said:
Hi

I have a field that can have either A, B or C as its value. I need to
write a query that will return the number of A's, B's & C's in the
field X for a given criteria and then the total records for the same
criteria. How do I write such as query?
Click to expand...

Here's example SQL (untested):

SELECT
Abs(Sum([X]='A') As CountOfAs,
Abs(Sum([X]='B') As CountOfBs,
Abs(Sum([X]='C') As CountOfCs,
Count(*) As TotalCount
FROM [TheTable]
WHERE SomeField = SomeCriterion;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Douglas J Steele said:
[X]='A' will return -1 (True) if the value of X is currently A, and 0
if it isn't.

Sum([X]='A') will sum all of those -1 and 0 values.

Abs(Sum([X]='A')) will convert the sum of -1 values to a positive
number. That value represents how many of the [X] values were equal
to 'A'

(note that Dirk inadvertently left out the final closing parenthesis
in the original expression)
Click to expand...

Thanks for catching that, Doug.
 
Back
Top