Calculating the Mode with multiple group bys

  • I'm new at this so hopefully someone can help.  I've seen a few examples but I can't get the correct result to return.

     

    Here is what I have.  But it doesn't quite work...Somewhere I'm dropping the Contracts

    SELECT p.Contract , MIN( p.MNTRVU ) AS CommonTRVU, p.Div_Cat

    FROM (

    SELECT p1.Contract,  p1.MNTRVU, p1.Div_Cat

    FROM 2004_Single_CPT p1

    GROUP BY p1.Contract, p1.MNTRVU, p1.Div_Cat

    HAVING COUNT( * ) = (SELECT MAX( Cnt )

                         FROM (SELECT COUNT( * ) AS Cnt

                               FROM 2004_Single_CPT p2

                               WHERE p2.Contract = p1.Contract

                               GROUP BY p2.MNTRVU

                         ) AS p3

                         )

    ) AS p

    GROUP BY p.Contract,p.Div_Cat

    I have the table 2004_Single_CPT and the fields I care about are;

     ~Contract

    ~MNTRVU

    ~Div_Cat

     There are about 25 unique Contracts that I’m trying to group by.  The MNTRVU holds the values I’m trying to get the Mode of.  Div_Cat has 3 unique values.

    I’m trying to Group by the Contract and by the Div_Cat and get the Mode of all the MNTRVU associated with these two fields.

    You’ll notice that the Contract Field contains ‘N/A’ and the MNTRVU contains Nulls. So in the Where statements I’ve been using ‘WHERE MNTRVU>0 or MNTRVU>0 and Contract <> 'N/A'’ to get rid of this.

    Any help would be just GREAT!

     

    Cheers!

  • Hi Max,

    "You’ll notice that the Contract Field contains ‘N/A’ and the MNTRVU contains Nulls. So in the Where statements I’ve been using ‘WHERE MNTRVU>0 or MNTRVU>0 and Contract <> 'N/A'’ to get rid of this."

    It seems to me that you only need WHERE MNTRVU > 0 and Contract <> 'N/A' as you need to exclude both items. If not Contracts = 'N/A'  are acceptable if MNTRVU > 0? And - if so,  are you properly bracketed e.g. WHERE MNTRVU>0 or (MNTRVU>0 and Contract <> 'N/A') and i'm just taking it as red that the " after N/A is just a typo.........

    Have Fun

     

    Steve

    We need men who can dream of things that never were.

  • Yep typo WHERE MNTRVU>0 and WHERE MNTRVU>0 is what I meant.  Anyone else want to take a stab at the answer?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply