# Thread: Statistics from One Table?

1. Registered User
Join Date
Nov 2003
Posts
300

## Unanswered: Statistics from One Table?

I am trying to summarize and count specific information from one table. Can I do this in one query?

I want it to return a sum of all the numeric values in COL A.
A count of all the text = Pending in COL B
A count of all the text = Information in COL C

When I structure the query, I get only a count of COL A and it seems to ignore COL B.

2. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
What does your query look like now? I see iif() statements in your immediate future...

3. Registered User
Join Date
Nov 2003
Posts
300
Hey Teddy, thanks, you may be right, but I made more of a mess with the iif statements!

Here is the SQL:
SELECT Count(Shipped.POUNDS) AS CountOfPOUNDS, Count(Shipped.[SHIP SPECIAL]) AS [CountOfSHIP SPECIAL], Count(Shipped.TRANSPORT) AS CountOfTRANSPORT
FROM Shipped
HAVING ((Shipped.[SHIP SPECIAL]="Pending") AND (Shipped.TRANSPORT="Information"));

I am expecting it to return something like this:

CountofPounds=12,000
CountofShippedSpecial=200
CountofTransport=390

but I get either all zeros or 12,000 in EACH of the above, obviously only looking at the first of my criteria....

I even thought of just a form with the statistics from this table,
would it be in a TEXT box? with an iif statement in each text box as you suggested? If that is better solution, tell me how!

4. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102
That makes sense. Count returns the number of records for a given field. Take a look at your "HAVING" clause. You are counting the number of records which have [SHIP SPECIAL] = "Pending" AS WELL AS Shipped.TRANSPORT = "Information". you are getting the number of records that match both criteria, naturally the number of records returned is going to be the same for each field.

Try something more like this:

SELECT SUM(shipped.pounds), SUM(iif(shipped.[ship special] = "pending", 1, 0)) AS [CountOfSHIP SPECIAL], SUM(iif(shipped.transport = "Information", 1, 0)) AS CountofTRANSPORT

We're talking three seperately aggregated values here, you have to present it as such.

5. Registered User
Join Date
Nov 2003
Posts
300
Yes! That did it.. Thanks!

I also need the actual ROW count or total records in that table but I guess I would have to make a seperate query for that..

again-thanks

6. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102