Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Count different values in one field

Status
Not open for further replies.

krets

Technical User
Dec 29, 2006
214
US
I was wondering if there is a way to create a query that would return the counts of each given value within one column. The example that I am working with is a database for tracking technician errors in a pharmacy. The technicians are assigned to work groups called cells. I've written a query to join several tables so I end up with:

TechInit(key) | CellNum | AITDate | RxNum(prescription number)| Severity | ErrorCode

Right now the Severity field is a combo with values of "Major" and "Minor". However, this could change to a High, Med, Low situation in the near future.

What the pharmacists would like to see is a report of the number of errors committed per cell with a count of the number of Major errors vs the number of Minor errors.

Is there a way to do that within a query?
 
yes, with the juditious use of the group by clause...

select
column1, count(column1)
from
table
group by
column1

--------------------
Procrastinate Now!
 
That counts the values but I would also like to group that by cell number so it would look like:

Code:
            | Total Errors | # of Maj | # of Min |
Cell Number |       #      |     #    |     #    |

I can get the total errors with a crosstab but I'm not sure how to count the individual values of "Major" and "Minor".
 
A starting point:
SELECT CellNum, Count(*) AS [Total Errors], Sum(IIf(Severity='Major',1,0)) AS [# of Maj]], Sum(IIf(Severity='Minor',1,0)) AS [# of Min]
FROM yourTable
GROUP BY CellNum

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Here's the query I was able to create after a little more Googling:

TRANSFORM Val(Nz(Count(qryAITbyCell.Key),0)) AS CountofKey
SELECT qryAITbyCell.CellNum, Count(qryAITbyCell.Key) AS Total
FROM qryAITbyCell
GROUP BY qryAITbyCell.CellNum
PIVOT qryAITbyCell.Severity In ("Major","Minor");

PHV: Is there a difference in performance or other significant factor that would make using your query a better idea?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top