Count

[Guest]

I need to find the top 20 most used answers. In this case the answers are
procedure codes.

Is there a way to do a frequency for each code, then report out the top 20??
Or some other way??

This code just gives me a total count of all codes: I need a count of each
distinct code, but I cannot use distinct because it only gives me 1 for each.

SELECT Count(tblTrackingData.TR_CPTCode) AS CountOfTR_CPTCode
FROM tblTrackingData;

 

[Guest]

Try this ---

SELECT TOP 20 tblTrackingData.TR_CPTCode, Count(tblTrackingData.TR_CPTCode)
AS CountOfCode
FROM [tblTrackingData]
GROUP BY tblTrackingData.TR_CPTCode
ORDER BY Count(tblTrackingData.TR_CPTCode) DESC;

If number twenty is tied with twenty-one then you will get 21 in the results.

 

[John Spencer]

If you don't want to show ties for the last position (I would show ties),
then you can add the TR_CPTCode to the sort.

SELECT TOP 20 tblTrackingData.TR_CPTCode
, Count(tblTrackingData.TR_CPTCode) AS CountOfCode
FROM [tblTrackingData]
GROUP BY tblTrackingData.TR_CPTCode
ORDER BY Count(tblTrackingData.TR_CPTCode) DESC
, tblTrackingData.TR_CPTCode

Try this ---

SELECT TOP 20 tblTrackingData.TR_CPTCode,
Count(tblTrackingData.TR_CPTCode)
AS CountOfCode
FROM [tblTrackingData]
GROUP BY tblTrackingData.TR_CPTCode
ORDER BY Count(tblTrackingData.TR_CPTCode) DESC;

If number twenty is tied with twenty-one then you will get 21 in the
results.

I need to find the top 20 most used answers. In this case the answers
are
procedure codes.

Is there a way to do a frequency for each code, then report out the top
20??
Or some other way??

This code just gives me a total count of all codes: I need a count of
each
distinct code, but I cannot use distinct because it only gives me 1 for
each.

SELECT Count(tblTrackingData.TR_CPTCode) AS CountOfTR_CPTCode
FROM tblTrackingData;