Need to add counter column that is partitioned by like row entries

First off, thanks to everyone that has attempted to help with my questions. It's greatly appreciated.

I have the following statement to pull the selected data and give SUM and AVG for Inp/Outp Amount & Count. Now that I have the data I need, I need to eliminate the issue that is causing extra rows for entries with the same ProcedureID, as the output is the same since it is partitioned based on ProcedureID. The idea I had was to add a counter column to the statement that is also partitioned to count by ProcedureID. Then SELECT the Highest or Lowest integer into a temptable and I'd be done.

SELECT  M.ProcedureID,
        M.SegmentDateTime,
    M.PriceID,
    L.DrugID,
    L.NdcDinNumber,
    L.Name,
    M.DeptCorporation,
    M.InpAmount,
    M.InpCount,
    M.OutAmount,
    M.OutCount,

       SUM(InpCount) OVER (PARTITION BY ProcedureID) as INtotal,
       SUM(InpAmount) OVER (PARTITION BY ProcedureID) as  IN$Total,
       SUM(OutCount) OVER (PARTITION BY ProcedureID) as OUTtotal,
       SUM(OutAmount) OVER (PARTITION BY ProcedureID) as  OUT$Total,
       SUM(InpCount + OutCount) OVER (PARTITION BY ProcedureID) as TotalCount,
       SUM(InpAmount + OutAmount) OVER (PARTITION BY ProcedureID) as TotalAmount
       AVG(InpCount + OutCount) OVER (PARTITION BY ProcedureID) as AverageCount,
       AVG(InpAmount + OutAmount) OVER (PARTITION BY ProcedureID) as AverageAmount

        FROM BarRevenueByProcedurePriceInfo M

LEFT JOIN DPhaDrugData L ON 
M.ProcedureID = L.BillNumber


    WHERE DeptID = '010.4730'
AND SegmentDateTime = '2013-12-31 00:00:00.000'
AND M.InpCount > '0'

OR

 DeptID = '010.4730'
AND SegmentDateTime = '2013-12-31 00:00:00.000'
AND M.OutCount > '0'

    ORDER BY ProcedureID

Since I'm still fairly new to SQL I figured I'd ask the experts. Thanks in advance!

Solution

Here are three possible methods to do what you want to do.

Here is a row_number() method:

with p as (
    SELECT M.ProcedureID, M.SegmentDateTime, M.PriceID,
           L.DrugID, L.NdcDinNumber, L.Name, M.DeptCorporation, M.InpAmount,
           M.InpCount, M.OutAmount, M.OutCount,
           row_number() over (partition by procedureID order by (select NULL)) as seqnum,
           SUM(InpCount) OVER (PARTITION BY ProcedureID) as INtotal,
           SUM(InpAmount) OVER (PARTITION BY ProcedureID) as  IN$Total,
           SUM(OutCount) OVER (PARTITION BY ProcedureID) as OUTtotal,
           SUM(OutAmount) OVER (PARTITION BY ProcedureID) as  OUT$Total,
           SUM(InpCount + OutCount) OVER (PARTITION BY ProcedureID) as TotalCount,
           SUM(InpAmount + OutAmount) OVER (PARTITION BY ProcedureID) as TotalAmount
           AVG(InpCount + OutCount) OVER (PARTITION BY ProcedureID) as AverageCount,
           AVG(InpAmount + OutAmount) OVER (PARTITION BY ProcedureID) as AverageAmount
    FROM BarRevenueByProcedurePriceInfo M LEFT JOIN
         DPhaDrugData L
         ON M.ProcedureID = L.BillNumber
    WHERE DeptID = '010.4730' AND SegmentDateTime = '2013-12-31 00:00:00.000' AND
          (M.InpCount > '0' or M.OutCount > '0')
   )
select p.*
from p
where seqnum = 1
ORDER BY ProcedureID;

You might want to remove seqnum from the list of removed columns.

Another method you can use is to just add distinct after the select.

Finally, I suspect your query is equivalent to:

    SELECT M.ProcedureID, M.SegmentDateTime, M.PriceID,
           L.DrugID, L.NdcDinNumber, L.Name, M.DeptCorporation, M.InpAmount,
           M.InpCount, M.OutAmount, M.OutCount,
           SUM(InpCount) as INtotal,
           SUM(InpAmount) as  IN$Total,
           SUM(OutCount) as OUTtotal,
           SUM(OutAmount) as  OUT$Total,
           SUM(InpCount + OutCount) as TotalCount,
           SUM(InpAmount + OutAmount) as TotalAmount
           AVG(InpCount + OutCount) as AverageCount,
           AVG(InpAmount + OutAmount) as AverageAmount
    FROM BarRevenueByProcedurePriceInfo M LEFT JOIN
         DPhaDrugData L
         ON M.ProcedureID = L.BillNumber
    WHERE DeptID = '010.4730' AND SegmentDateTime = '2013-12-31 00:00:00.000' AND
          (M.InpCount > '0' or M.OutCount > '0')
    GROUP BY M.ProcedureID, M.SegmentDateTime, M.PriceID,
             L.DrugID, L.NdcDinNumber, L.Name, M.DeptCorporation, M.InpAmount,
             M.InpCount, M.OutAmount, M.OutCount;