If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count
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; |
#2
|
|||
|
|||
Count
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. "Dan @BCBS" wrote: 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; |
#3
|
|||
|
|||
Count
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 "KARL DEWEY" wrote in message ... 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. "Dan @BCBS" wrote: 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; |
Thread Tools | |
Display Modes | |
|
|