Phi Zeroth Phi Zeroth - 3 months ago 9
SQL Question

Top item for each top 5 group by record count (Access SQL)

I have a single large table

Audit
with thousands of records of denied claims. Each one comes from a supplier and has a denial reason and a status. Only the
id
is unique.

id | denial | supplier | status
---|------------------|---------------|---------
1 | Duplicate claim | ACME | Adjusted
2 | Not authorized | Umbrella Corp | Adjusted
3 | Not authorized | Stark Ind. | Adjusted
4 | Rec'd after due | ACME | Override
5 | Duplicate claim | Stark Ind. | Adjusted
... etc


What I need is the top 5 denial reasons for "Adjusted" status claims by record count, and the top provider by record count for each of those denial reasons.

denial | cnt_denial | top_supplier | cnt_top_supplier
----------------|------------|---------------|-----------------
Not authorized | 917 | Stark Ind. | 351
Duplicate claim | 685 | Stark Ind. | 195
Not in contract | 525 | ACME | 216
Rec'd after due | 512 | Umbrella Corp | 500
Explosions | 349 | ACME | 231


I've tried lots of ways, mostly trying to adapt other somewhat similar solutions, but I'm overreaching my meager SQL knowledge here and getting frustrated. Several solutions I've tried do not work in MS Access (2010). I created two queries as a start, but I'm having trouble joining them in the way that I need.

This query returns exactly the denial reason data that I need:

SELECT TOP 5 denial, Count(*) AS cnt_denial
FROM Audit
GROUP BY status, denial
HAVING status="Adjusted"
ORDER BY Count(*) DESC;


And this query has a count of every denial/supplier grouping in the table. I'm not sure how to get the top supplier for each denial reason, which seems like it should be simple but I'm having trouble. EDIT: This is my primary problem. If I can just get the top single supplier for each denial reason I can figure out the join. I've tried using MAX but haven't succeeded yet. /EDIT

SELECT denial, supplier, Count(*) AS cnt_supplier
FROM Audit
GROUP BY denial, supplier, status
HAVING status="Adjusted"
ORDER BY Count(*) DESC;


I need a single query since I'll just be passing this from Excel via ADO. Any help would be hugely appreciated.

Answer

I don't know if this is perfectly valid Access SQL. It shouldn't require much tweaking if it isn't:

select
    d.denial,
    d.cnt_denial,
    ds.supplier as top_supplier,
    ds.cnt_supplier as cnt_top_supplier
from
    (
        select top 5 denial, count(*) as cnt_denial
        from Audit
        where status = 'Adjusted'
        group by denial
        order by count(*) desc
    ) d
    inner join
    (
        select denial, supplier, count(*) as cnt_supplier
        from Audit
        where status = 'Adjusted'
        group by denial, supplier
    ) ds
        on ds.denial = d.denial
where not exists (
    select 1
    from Audit as a2
    where a2.status = 'Adjusted'
        and a2.denial = ds.denial and a2.supplier <> ds.supplier
    group by a2.supplier
    having count(*) > ds.cnt_supplier
        /* or count(*) = ds.cnt_supplier and a2.supplier < ds.supplier -- tiebreaker */
)
order by d.cnt_denial desc, ds.supplier

By using the top 5 it's grabbing only five rows regardless of ties. Then the top 1 per group is handled with a subquery and in that case you would include ties and potentially retrieve more than five rows in the final result. It's not too difficult to include ties at the denial level if you need that or to break ties and exclude them in the supplier ranking.

EDIT: I threw together some test data and the tie-breaking appears to work on SQL Server. http://rextester.com/ZEWJ43486