CODEWITHSUNDEEP
sqldatabasesql-order-byaggregate-functions
Alvin
Alvin

Reputation: 515

Difficult sql about aggregate and sorting

I would like some help for the following question, thanks.

Original Table:

id, mark
1, 3
2, 4
3, 5
4, 10
5, 14

Result Table

id, mark
1, 36
2, 33
3, 29
4, 24
5, 14

How to use to get the result table from the original table by sql? I have noticed that the mark field in the result table is the sum of others, but no idea how to achieve that.

Upvotes: 0

Views: 55

Answers (2)

peter.petrov
peter.petrov

Reputation: 39477

This seems simple. Try this.

SELECT t1.id, 
       Sum(t2.mark) 
FROM   originaltable t1 
       JOIN originaltable t2 
         ON t1.id <= t2.id 
GROUP  BY t1.id 
ORDER  BY t1.id ASC

Upvotes: 5

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186803

This is a good case to use analytic functions if your DBMS supports them:

  select id,
         sum(mark) over (order by id desc)
    from Original

Upvotes: 2

Related Questions