SQL Server: filter out results based on different data in same column

Question

I ran into a issue with a query I'm creating.

The table looks like this:

LineId|Product|orderedQty|PickedQty|Operator|TimeFinised
------+-------+----------+---------+--------+-----------
     1|   1234|        60|       40|    Joe |     125546      
     2|   1234|        60|       18|  Benny |     120025

I would like to create a query that turns this into one result where the PickedQty are summed up. I would also like to only show the last operator that picked the items based on the column Timefinished.

The result should look like this:

Product|orderedQty|PickedQty|Operator
   1234|        60|       58|     Joe

Query so far:

 select
     product,
     orderedQty,
     cast(sum(m.PickedQty) as decimal(18,1)) as PickedQty,
 from 
     Lines
 group by 
     product, orderedQty

It's easy enough to summarize the PickedQty, but I have no idea how I can filter out the first operator.

Do anyone know how I can achieve this?

question from:https://stackoverflow.com/questions/65847848/sql-server-filter-out-results-based-on-different-data-in-same-column

Answer 1

Conditional aggregation may help:

select
   product,
   orderedQty,
   cast(sum(m.PickedQty) as decimal(18,1)) as PickedQty,
   MAX (CASE WHEN Rn = 1 THEN Operator END) AS Operator
from (
   SELECT 
      *,
      ROW_NUMBER() OVER (PARTITION BY product, orderedQty ORDER BY TimeFinised DESC) AS Rn
   FROM (VALUES
       (1, 1234, 60, 40, 'Joe',   125546),      
       (2, 1234, 60, 18, 'Benny', 120025)
   ) v (LineId, Product, orderedQty, PickedQty, Operator, TimeFinised)   
) m
group by 
   product, orderedQty

Result:

product orderedQty  PickedQty   Operator
1234    60          58.0        Joe