CODEWITHSUNDEEP
sqlsql-servergroup-bysumwindow-functions
Lucas Prestes
Lucas Prestes

Reputation: 402

Query with groupby sum

I need a query with sum from a column inside a group by

I have a query like this

select a.type, a.description, a.value  
 from A  
 group by a.type, a.description, a.value

this will return something like:

| type | description | value |
|------|-------------|-------|
| X    | test 1      | 5     |
| X    | test 2      | 9     |
| Y    | test 3      | 15    |

I need a sum of value from group by Type in a new column, like

| type | description | value | total |
|------|-------------|-------|-------|
| X    | test 1      | 5     |       |
| X    | test 2      | 9     | 14    |
| Y    | test 3      | 15    | 15    |

Can someone help me, Please?

Upvotes: 0

Views: 48

Answers (2)

Somy
Somy

Reputation: 1624

Assuming the ordering be done using the number part of the description column, you may use the logic below.

select 
    type, 
    description, 
    value , 
    sum(value) over(partition by type order by cast(substring(description,6,len(description)+1-6) as int)) total
from a
group by type, description, value;

Upvotes: 0

GMB
GMB

Reputation: 222412

Uuse window functions. Starting from your existing query:

select 
    type, 
    description, 
    value , 
    sum(value) over(partition by type order by value) total
from a
group by type, description, value

This assumes that you want to order the rows by value.

I am wondering whether you originally meant to sum() the values in the original query: that's what your question seems to say (and aggregation without aggregate functions usually makes little sense). In that case, you would do:

select 
    type, 
    description, 
    sum(value) value, 
    sum(sum(value)) over(partition by type order by value) total
from a
group by type, description

Upvotes: 1

Related Questions