CODEWITHSUNDEEP
sqlselectaggregate
user3133542
user3133542

Reputation: 1715

SQL: Column Sum

Lets have following sample table:

Person Quantity
A      1
B      2
C      3
D      4
E      5

Result should be:

PersonAggregate
1     (0+Quantity of PersonA)=sumA
3     (sumA+Quantity of PersonB)=sumB
6     (sumB+Quantity of PersonC)=sumC
10    (sumC+Quantity of PersonD)=sumD
15    (sumD+Quantity of PersonE)

Is it possible to get this result in onq SQL-query?

Upvotes: 0

Views: 92

Answers (3)

Brian DeMilia
Brian DeMilia

Reputation: 13248

If the sample table has more than one row per person with multiple quantities that need to be summed you could use:

select curr.person, curr.sum_person + case when prev.person <> curr.person
                                           then prev.sum_person
                                           else 0 end as person_sum
  from (select person, sum(quantity) as sum_person
          from sample
         group by person) curr
 cross join (select person, sum(quantity) as sum_person
               from sample
              group by person) prev
 where prev.person =
       (select max(x.person) from sample x where x.person < curr.person)
    or curr.person = (select min(person) from sample)
 group by curr.person

Fiddle: http://sqlfiddle.com/#!2/7c3135/6/0

Output:

| PERSON | PERSON_SUM |
|--------|------------|
|      A |          1 |
|      B |          3 |
|      C |          5 |
|      D |          7 |
|      E |          9 |

If there is only one row per person on the sample table, you could more simply use:

select curr.person, curr.quantity +   case when prev.person <> curr.person
                                           then prev.quantity
                                           else 0 end as person_sum
  from sample curr
 cross join sample prev
 where prev.person =
       (select max(x.person) from sample x where x.person < curr.person)
    or curr.person = (select min(person) from sample)
 group by curr.person

Fiddle: http://sqlfiddle.com/#!2/7c3135/8/0

Output returned is the same, because in your example, there is only one row per person.

If using Oracle, SQL Server, or a database that supports analytic functions, you could use:

If sample has one row per person:

select person,
       sum(quantity) over(order by person rows between 1 preceding and current row) as your_sum
  from sample
 order by person

Fiddle: http://sqlfiddle.com/#!4/82e6f/1/0

If sample has 2+ rows per person:

select person,
       sum(sum_person) over(order by person rows between 1 preceding and current row) as your_sum
  from (select person, sum(quantity) as sum_person
          from sample
         group by person) x
 order by person

Fiddle: http://sqlfiddle.com/#!4/82e6f/4/0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Most versions of SQL support cumulative sums as a window function:

select person, sum(quantity) over (order by person) as cumesum
from sample;

You can can also do this with a correlated subquery:

select s.person,
       (select sum(s2.quantity)
        from samples s2
        where s2.person <= s.person
       ) as cumesum
from sample s;

Upvotes: 3

duffymo
duffymo

Reputation: 308928

this will obviously get the individual sums.

select person, sum(quantity)
from sample
group by person
order by person

i don't think your desired effect can be done in a set based way. a procedural language with cursor, like T-SQL or PLSQL, can do it easily.

i'd write a stored procedure and call it.

Upvotes: 1

Related Questions