CODEWITHSUNDEEP
sqlms-accessvba
JeffO
JeffO

Reputation: 137

Select MAX for multiple criteria in a group

Apologies if this has been answered, I'm new enough that I didn't even know how to search:

I have one table:

Lot SKU Cost Date

1001-1 1001 .30 10-12-14
1001-2 1001 .33 10-19-14
1001-3 1001 .32 11-20-14
1002-1 1002 .45 10-12-14
1002-2 1002 .45 10-19-14
1002-3 1002 .44 12-01-14
1003-1 1003 .12 10-15-14
1003-2 1003 .13 10-19-14
1003-3 1003 .10 11-23-14

i need to sum the cost of the oldest row for each SKU.

expected outcome: (.30 + .45 + .12) = .87

is this possible through one query?

Upvotes: 0

Views: 1218

Answers (2)

Nicholas Carey
Nicholas Carey

Reputation: 74177

This should work:

select sum(t.cost)
from some_table t
join ( select sku ,
              min(some_datetime_column) oldest
       from some_table
       and some_datetime_column is not null
       group by sku
     ) s on s.sku    = t.sku
        and s.oldest = t.some_datetime_column

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

ANSI SQL support a function called row_number(), which can be very helpful for this type of query. The following is how you would use it in this case:

select sum(cost)
from (select t.*, row_number() over (partition by sku order by date) as seqnum
      from table t
     ) t
where seqnum = 1;

Upvotes: 1

Related Questions