Calculate Sum of comma separated value from columns in SQL Server

Last Reply 11 months ago By dharmendr

Posted 11 months 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.

You are viewing reply posted by: dharmendr 11 months ago.
Posted 11 months ago Modified on 11 months 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(2,'Sadriddin','2,3,1,4','1,1,1,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