CODEWITHSUNDEEP
sqlsql-servert-sql
Alex Martinez
Alex Martinez

Reputation: 201

Create extra sum column using one of the table columns value

I have a table data like this:

Product     Value
A           20 
A           30
B           10
A           20
C           15
C           15

I need to get the sum based on the Value column like:

Product     Value     Sum
A           20         70
A           30         70
B           10         10
A           20         70
C           15         30
C           15         30

How do I query to create the sum of all A, B and C product still showing all records?

Upvotes: 2

Views: 53

Answers (3)

Ilyes
Ilyes

Reputation: 14928

Here you go

CREATE TABLE T(
  Product VARCHAR(20),
  Value INT
);

INSERT INTO T VALUES
('A', 20), 
('A', 30),
('B', 10),
('A', 20),
('C', 15),
('C', 15);

SELECT *, (SELECT SUM(Value) FROM T WHERE Product = T1.Product) [Sum]
FROM T T1;

Returns:

+---------+-------+-----+
| Product | Value | Sum |
+---------+-------+-----+
| A       |    20 |  70 |
| A       |    30 |  70 |
| B       |    10 |  10 |
| A       |    20 |  70 |
| C       |    15 |  30 |
| C       |    15 |  30 |
+---------+-------+-----+

Upvotes: 3

user6691848
user6691848

Reputation:

You can achieve this using a window function for sum:

create table #totalValue
(
[Product] varchar(55),
Value int
)

insert into #totalValue
values
('A',20), 
('A',30),
('B',10),
('A',20),
('C',15),
('C',15)


select 
    Product,
    [Value],
    sum([Value]) over (partition by Product) as [Sum]
from #totalValue

This should scale better than a solution that queries the same table twice.

Upvotes: 4

Jayesh Goyani
Jayesh Goyani

Reputation: 11154

Please try with the below code snippet.

select a.product, a.value, b.totalsum
from producttable as a
left join ( select sum(c.value) as totalsum,c.product 
            from producttable as c 
            group by c.product
           ) as b
on a.product = b.product

Upvotes: 3

Related Questions