Calculate Sum of comma separated value from columns in SQL Server

Last Reply 7 months ago By dharmendr

Posted 7 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 7 months ago.
Posted 7 months ago Modified on 7 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