select the sum of every client's last order

Question

Hello,

My data is as follows:

ORDER_ID   CLIENT_ID   DATE     VALUE
        1881    51  2010-07-19  100.17
        1882    50  2010-07-19  100.17
        2754    50  2010-07-25  135.27
        2756    50  2010-07-25  100.28
        5514    50  2010-07-27  121.76
        5515    50  2010-07-28  109.59
        5516    50  2010-07-27  135.29
        5517    50  2010-07-28  121.77
        5518    50  2010-07-31  123.15
        5519    50  2010-07-31  123.16
        5520    50  2010-07-31  109.62
        6079    51  2010-07-31  100.33
        7372    50  2010-07-25  100.27

What I want is to specify an initial date, for example, '2010-07-27', to filter only records at or after this date in the WHERE clause; the query should get the latest order from client 50 (order 1881) and client 51 (order 5516) and sum them together. I know it's simple and I tried in some different ways but couldn't find the right path. I guess I'm thinking narrow today, so I'm turning to you for help.

Thank you.

Answer 1

Let's give this a try:

SELECT CLIENT_ID, SUM(VALUE)
FROM YourTable
WHERE DATE >= '7/27/2010'
GROUP BY CLIENT_ID

Unless I've misread or misunderstood your question. :)

Edit: Let's try this, given the feedback:

SELECT SUM(VALUE)
FROM MyTable mt,
(SELECT CLIENT_ID, MAX([DATE]) AS 'Date'
FROM MyTable
GROUP BY CLIENT_ID) AS r
WHERE mt.CLIENT_ID = r.CLIENT_ID
AND mt.DATE = r.Date

Edit again: Basing last order on ORDER_ID instead of DATE:

SELECT SUM(VALUE)
FROM MyTable mt,
(SELECT CLIENT_ID, MAX([ORDER_ID]) AS 'ORDER_ID'
FROM MyTable
GROUP BY CLIENT_ID) AS r
WHERE mt.CLIENT_ID = r.CLIENT_ID
AND mt.ORDER_ID = r.ORDER_ID

Answer 2

You can use a subquery to filter orders for which no later order exists, like:

select  sum(t1.value)
from    YourTable t1
where   t1.Client_ID in (50, 51)
        and t1.Date > '2010-07-27'
        and not exists
        (
        select  *
        from    YourTable t2
        where   t1.Client = t2.Client
                and 
                (
                    t1.Date < t2.Date
                    or
                    (t1.Date = t2.Date and t1.Order_ID < t2.Order_ID)
                )
        )

A group by is not required if you only select the sum.

Your comment suggests you're looking for the FIRST (not latest) order after 2010-07-27. If that's the case, replace the subquery condition with:

                (
                    t1.Date > t2.Date
                    or
                    (t1.Date = t2.Date and t1.Order_ID > t2.Order_ID)
                )

Answer 3

SELECT

SUM
(
  (SELECT TOP(1) VALUE
   FROM ORDER
   WHERE
   CLIENT_ID=C.CLIENT_ID
   DATE >= @LBOUND_ORDER_DATE
   ORDER BY DATE DESC
  )
) AS TotalLatestOrders

FROM

CLIENT C

Answer 4

why 1881 and 5516 ? I think the last order for this 2 client are 5520 and 6079. below is mine: select sum(value) from ( select value, row_number() over (partition by client_id order by order_id desc) as sn from tablename where date > '2010-7-27' ) T where sn = 1

Answer 5

I would prefer not using a subquery as they can slow a query down as the table gets larger...

how about this:

SELECT SUM(VALUE) AS SumOfOrderValues
FROM
    YourTable t 
    INNER JOIN (
        SELECT CLIENT_ID, MAX(ORDER_ID) AS MaxOrderId
        FROM YourTable
        WHERE [DATE] >= '2010-07-27'
        GROUP BY CLIENT_ID
    ) AS m ON m.MaxOrderId = t.ORDER_ID

This should give you this latest order for each client on or after a certain date. Then the SUM of the Values for the specific orders.