Access Mode in query

[DRANDON]

I have "Payer Desc", "DRG", "Expected" fields in a table. I need the mode (frequency of occurrence) for "Expected" for each "DRG" by "Payer Desc".
Can someone please help me? I will be greatly appreciative. Thank you.

 

[xenou]

This would be frequency or count, if that helps (but I would not really say it is mode in the statistical sense)

SELECT 
    DRG,
    [Payer Desc],
    Expected,
    COUNT(Expected) AS CountOfExpected
FROM Table1
GROUP BY DRG, [Payer Desc]
ORDER BY DRG, [Payer Desc]

 

[DRANDON]

This would be frequency or count, if that helps (but I would not really say it is mode in the statistical sense)

SELECT
    DRG,
    [Payer Desc],
    Expected,
    COUNT(Expected) AS CountOfExpected
FROM Table1
GROUP BY DRG, [Payer Desc]
ORDER BY DRG, [Payer Desc]

I can get the count of Expected. What I need next to get the maximum "count" of expected and exclude all the others that aren't max.
My query using totals Group by all fields except CountofExpected which is "max" does not work.
It still gives me all the other DRG's that have lesser frequencies...

This would be frequency or count, if that helps (but I would not really say it is mode in the statistical sense)

SELECT
    DRG,
    [Payer Desc],
    Expected,
    COUNT(Expected) AS CountOfExpected
FROM Table1
GROUP BY DRG, [Payer Desc]
ORDER BY DRG, [Payer Desc]

So I created a query to just get the max count of Expected. Then another query that pulled from the original query output linked to the max count and it worked like a charm.