SQL select row with max value or distinct value and sum all

I have the following data that is returned to me. I need to get a distinct or max sum of all the commission by taxid for a single repnbr. The ‘qtrlycommrep’ column is the value I’m trying to get to, but not able to. For repnbr c590, I need to get the 854.66 commission amount, which is the max for each taxid.

What am I doing wrong?

Any help would be much appreciated!

Here’s what I’ve tried so far. Using the Row_number

select distinct 
        sub.Repnbr
    ,   (sub.QtrLYComm) as qtrlycommrep
   from ( 
        select distinct repnbr, QtrLYComm
        , rn = row_number() over(partition by repnbr order by QtrLYComm desc)

    from #qtrly
    ) sub
    where sub.rn = 1

Cross Apply

 select distinct
        #qtrly.repnbr
    ,   x.QtrLYComm as qtrlycommrep

    from #qtrly
        cross apply (
            select top 1
                *
            from #qtrly as i
            where i.repnbr = Repnbr
            order by i.qtrlycomm desc
            ) as x;

inner join

select
    #qtrly.repnbr, #qtrly.qtrlycomm as qtrlycommrep

 from #qtrly 
    inner join (
    select maxvalue = max(qtrlycomm), repnbr
    from #qtrly
    group by repnbr
    ) as m
    on #qtrly.repnbr = m.repnbr 
    and #qtrly.qtrlycomm = m.maxvalue;

order by row_number

  select top 1 with ties
        #qtrly.repnbr, #qtrly.qtrlycomm as qtrlycommrep

    from #qtrly 
        order by 
            row_number() over(partition by repnbr 
            order by qtrlycomm desc)

Answer

You want one value per tax id. You need to include that. For instance:

select q.Repnbr, sum(q.QtrLYComm) as qtrlycommrep
from (select q.*,
             row_number() over(partition by repnbr, taxid order by QtrLYComm desc) as seqnum
      from #qtrly q
     ) q
where seqnum = 1
group by q.Repnbr;

However, I would be inclined to use two levels of aggregation:

select q.Repnbr, sum(q.QtrLYComm) as qtrlycommrep
from (select distinct repnbr, taxid, QtrLYComm
      from #qtrly q
     ) q
group by q.Repnbr;

Advertisement

Answer