SQL query to get the sum and the latest enries

Question

I have a table called machineStatus:

ID  Successfiles totaldata Backupsessiontime
> 1 3   988   1256637314
> 2 21   323   1256551419
> 3 8   23   1256642968
> 4 94   424   1256642968
> 1 42   324   1256810937
> 1 0   433   1256642968

Now here i want to group by ID where the successfiles and total data gets summed up, but only display the latest Backupsessiontime. I can do this seperately but not together. Any suggestions????

For doing this seperately:

to get the sum:

select ID, sum(NumOfSuccessFiles), sum(TotalData)
from MachineStat
 group by ID;

to get latest:

With idT as (
select ID
from MachineStat
group by ID

) 
select applyT.* 
from idT p
CROSS APPLY (
select top 1 ID,BackupSessionTime from MachineStat where eID=p.ID   
order by MachineID desc
) as applyT

Answer 1

You can use an aggregate function that would suit your needs:

select
    ID,
    sum(NumOfSuccessFiles) TotalNumOfSuccessFiles,
    sum(TotalData) TotalData,
    max(Backupsessiontime) LastBackupsessiontime
from
    MachineStat ms
group by
    ID

Or you can use a sub-query in your primary query, if you need more complicated sorting logic.

select
    ID,
    sum(NumOfSuccessFiles) TotalNumOfSuccessFiles,
    sum(TotalData) TotalData,
    (select top 1 Backupsessiontime from MachineStat where ID = ms.ID order by ...) LastBackupsessiontime
from
    MachineStat ms
group by
    ID

Answer 2

Max(BackupSessionTime) ?

Answer 3

It looks like you want to do

select ID, sum(NumOfSuccessFiles), sum(TotalData), max(Backupsessiontime) from MachineStat group by ID;