CODEWITHSUNDEEP
sqlt-sql
Jack
Jack

Reputation: 29

How to handle tie with mode calculation SQL

Guys this task just blowing my mind. I have sql view that calculates MODE but when it ties it just return first value now I need return value only when single MODE exist in other case NULL. Here is code of my VIEW

WITH cteModeGroups AS
(
  SELECT 
    -- the partitioning dimensions:
    wd.AntibioticID,  
    wd.SiteID,
    wd.AccessionIdentity,
    
    -- the measure:
    wd.AntibioticConcentration, -- which in the result is the actual mode value, i.e. the concentration value that occurs most often in the group
    wd.MICType, -- the qualifier that says =, <= or > 
    Max(pd.DateCreated) as MostRecentPlateRunDate,
    
    ROW_NUMBER() OVER(PARTITION by wd.SiteID, wd.AccessionIdentity, wd.AntibioticID, wd.MICType ORDER BY count(1) desc ) AS RowNumber,
    COUNT(1) as GroupCount 
  FROM ref.vwMicData wd
  INNER JOIN [ref].[PlateData] pd on pd.ID = wd.PlateDataID
  where wd.MIC = 1 
  and wd.bad = 0
  and wd.AntibioticID > -1
  group by wd.AntibioticConcentration, wd.SiteID, wd.AccessionIdentity, wd.AntibioticID, wd.MICType
)

  select 
cmg1.SiteID,
cmg1.AccessionIdentity, 
cmg1.AntibioticID, 
cmg1.AntibioticConcentration, 
cmg1.MICType,
cmg1.RowNumber,
cmg1.GroupCount,
cmg1.MostRecentPlateRunDate,
plates.MicPlateCount
    
    
from cteModeGroups cmg1


left join cteModeGroups cmg2 on cmg2.SiteID=cmg1.SiteID
                        and cmg2.AccessionIdentity=cmg1.AccessionIdentity
                        and cmg2.AntibioticID=cmg1.AntibioticID
                        and cmg2.GroupCount > cmg1.GroupCount

left join (
    select      
        vmd.AccessionIdentity,
        vmd.AntibioticID,
        count(distinct vmd.PlateDataID) as MicPlateCount
    from ref.vwMICData vmd
    where vmd.MIC=1
    and vmd.AntibioticID > -1
    and vmd.bad=0
    --and vwd.BacteriaPresent = 1
    --and vwd.Contaminated = 0
    group by vmd.AntibioticID, vmd.AccessionIdentity
) Plates on Plates.AntibioticID = cmg1.AntibioticID and Plates.AccessionIdentity = cmg1.AccessionIdentity

where 
    
    cmg2.SiteID is  null

Here is result of this query (highlighted equal groups need get NULL in this case) enter image description here

thanks for any help.

Upvotes: 0

Views: 373

Answers (2)

GarethD
GarethD

Reputation: 69789

You are very much over complicating this, and you have already done the hard work. Within your CTE you already calculate a row number, all you need to do is use it to remove the duplicates:

enter image description here

This means you can also remove your left join:

WITH cteModeGroups AS (...)
SELECT  cmg1.SiteID,
        cmg1.AccessionIdentity,
        cmg1.AntibioticID,
        cmg1.AntibioticConcentration,
        cmg1.MICType,
        cmg1.RowNumber,
        cmg1.GroupCount,
        cmg1.MostRecentPlateRunDate,
        Plates.MicPlateCount
FROM    cteModeGroups AS cmg1
        LEFT JOIN
        (
            SELECT  vmd.AccessionIdentity, 
                    vmd.AntibioticID, 
                    MicPlateCount = COUNT(DISTINCT vmd.PlateDataID)
            FROM    ref.vwMICData AS vmd
            WHERE   vmd.MIC = 1
            AND     vmd.AntibioticID > -1
            AND     vmd.bad = 0
            GROUP BY
                    vmd.AntibioticID, vmd.AccessionIdentity
        ) AS Plates
            ON  Plates.AntibioticID = cmg1.AntibioticID
            AND Plates.AccessionIdentity = cmg1.AccessionIdentity
WHERE   cmg.RowNumber = 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I can't really follow your question. But, here is the idea to attach the mode from a generic table, with NULL if there are duplicates:

select t.*, cm.mode
from t outer apply
     (select col as mode
      from (select col, count(*) as cnt,
                   row_number() over (order by count(*) desc) as seqnum,
                   count(*) over (partition by count(*)) as cnt_cnt
            from t
            group by col
           ) cm
       where cnt = 1 and cnt_cnt = 1
      ) cm;

The idea is to use window functions both to order by the count(*) and to count the number of values with the count.

Upvotes: 1

Related Questions