# Calculate Sum of comma separated value from columns in SQL Server

## Last Reply one year ago By dharmendr

Posted one year ago

Hi! I have two table in sql with data. But I want multiple number with price and show summa.

 Id Name Price 1 Apple 5 2 Orange 13 3 Oil 25 4 Cherry 12

 Id Person IdFood NumberKg 1 Rustam 1,3,4 2,3,1 2 Sadriddin 2,3,1,4 1,1,1,1 3 Nurullo 4 3

For example:

Apple id=1 and price=5, number=2, price multiple number =10

Orange id=2 and price=13, number=3, price multiple number =39

Cherry id=4 and price=12, number=1, price multiple number =12

summa =61 only for Rustam. Its need for all person.

Posted one year ago Modified on one year ago

Hi PRA,

Refer the below example.

For splitString function refer below article.

Split and convert Comma Separated (Delimited) String to Table in SQL Server

SQL

```DECLARE @Fruits AS TABLE(Id INT,Name VARCHAR(10),Price INT)
INSERT INTO @Fruits VALUES(1,'Apple',5)
INSERT INTO @Fruits VALUES(2,'Orange',13)
INSERT INTO @Fruits VALUES(3,'Oil',25)
INSERT INTO @Fruits VALUES(4,'Cherry',12)

DECLARE @Person AS TABLE(Id INT,Person VARCHAR(10),IdFood VARCHAR(50),NumberKg VARCHAR(50))
INSERT INTO @Person VALUES(1,'Rustam','1,2,4','2,3,1')
INSERT INTO @Person VALUES(3,'Nurullo','4','3')

DECLARE @Result AS TABLE(Id INT,Person VARCHAR(10),Fruit INT,Number INT)

DECLARE @Counter INT
SET @Counter = 1
WHILE (@Counter <= (SELECT COUNT(*) FROM @Person))
BEGIN
DECLARE @Id INT,@Name VARCHAR(10),@Total INT,@Food VARCHAR(50),@Number VARCHAR(50)
SET @Id = (SELECT Id FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,Id FROM @Person) t WHERE t.SiNo = @Counter)
SET @Name = (SELECT Person FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,Person FROM @Person) t WHERE t.SiNo = @Counter)
SET @Food = (SELECT IdFood FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,IdFood FROM @Person) t WHERE t.SiNo = @Counter)
SET @Number = (SELECT NumberKg FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,NumberKg FROM @Person) t WHERE t.SiNo = @Counter)

DECLARE @CounterInner INT
SET @CounterInner = 1
WHILE (@CounterInner <= (SELECT COUNT(Item) from dbo.SplitString(@Food,',')))
BEGIN
DECLARE @FoodId INT, @NumberKg INT
SET @FoodId = (SELECT Item FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,Item from dbo.SplitString(@Food,',')) t WHERE t.SiNo = @CounterInner)
SET @NumberKg = (SELECT Item FROM (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) SiNo,Item from dbo.SplitString(@Number,',')) t WHERE t.SiNo = @CounterInner)
INSERT INTO @Result VALUES(@Id,@Name,@FoodId,@NumberKg)

SET @CounterInner = @CounterInner  + 1
END

SET @Counter = @Counter  + 1
END

SELECT r.Id,r.Person,SUM(f.Price * r.Number) Summa FROM @Result r
INNER JOIN @Fruits f ON f.Id = r.Fruit
GROUP BY r.Id,r.Person```

Output

 Id Person Summa 1 Rustam 61 2 Sadriddin 55 3 Nurullo 36