RowNumber() and SUM() in one query

Question

is there some way who to get last record using rownumber() and SUM of one field (money in this case)? I've tried to come up with a query like:

SELECT 
        [date]
        ,...
    FROM
        (
        SELECT
             CAST(t.timestamp AS DATE)              AS [date]
            ,.../some fields/
            ,row_number() over (partition by ca.logical_number order by t.timestamp DESC) as rownumber --last update(record) transaction
            --,amount_transferred = 
            --(
            --  SELECT
            --      ,SUM(t.money_value)     AS  amount_transferred
            --   FROM
            --      TO_Transaction t
            --  GROUP BY
            --      CAST(t.timestamp AS Date)
            --)

        ) AS t
    WHERE rownumber=1

What the query is supposed to do is to find current purse balance and all money transferred during a day. Any help would be aprreciated. Thanks.

Solution

you can also do sum(field) over (...)

select
  row_number() over (partition by ca.logical_number order by t.timestamp DESC) as rownumber,
  sum(amount_transfered) over (partition by ca.logical_number ) as total_amount_transfered
from ...

Comments

[Jamiec]

Thanks @Gordon - order by makes no sense in a sum.

[Admin]

My godness, it probably works! :-) Will mark as right answer when the time needed before expires.

[Jamiec]

@StanislavJirák shocking!

[Admin]

I'm new to SQL and after some time spent, I was going to split the procedure into 2 pieces.

[Jamiec]

I'm old to sql and the day i realised i could add an over() to any aggregate function was a good day!

[Admin]

Yes, it seems over() is quite handy. This is good day fro me!