December 16, 2004 at 5:28 pm
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;
~MNTRVU
~Div_Cat
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.
December 17, 2004 at 5:47 am
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.
December 17, 2004 at 8:22 am
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