Group count query advise

[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

 

[Dirk Goldgar]

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?

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;

 

[John]

Thanks.

Dumb question: Why do we need the abs function?

Regards

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?

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)

 

[TC]

Or if you do not know what all the values in the field X might be:

SELECT X, COUNT(*)
FROM YourTable
GROUP BY X

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Dirk Goldgar]

Thanks.

Dumb question: Why do we need the abs function?

Regards

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;

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.

 

[John Vinson]

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

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?

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)

 

[TC]

Personally, I would use:

SELECT
Sum (IIF ([X]='A', 1, 0)) As CountOfAs,
etc.

Then it doesn't matter what is used "behind the scenes" for true &
false.

Cheers,
TC (MVP Access)
http://tc2.atspace.com

 

[John]

Thanks for this gem. Pure poetry.

Regards

Thanks.

Dumb question: Why do we need the abs function?

Regards

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;

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)

 

[Michel Walsh]

Hi,


can also try a crosstab like:


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


Vanderghast, Access MVP

 

[Guest]

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.

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?

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)

 

[Douglas J Steele]

[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!)

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.

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?

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)

 

[Dirk Goldgar]

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

Thanks for catching that, Doug.