Query to get maximum SUM based on another column value in SQL Server

Last Reply 2 months ago By dharmendr

Posted 2 months ago

Hi! I used below code, but couldn’t get requirement result.

Fruits

Id

name

price

1

apple    

5

2

orange  

4

3

onion    

6

4

tomato  

9

Person

id

pname

1

Rustam        

2

Firuz        

3

Nurullo      

Cash

id

idperson

customername

idfruit

numberprice

1

1

Suhrob  

1,3      

1

2

3

Amonullo

2,3      

2

3

1

Suhrob  

3,1      

3

4

2

Alijon  

1,3      

4

I want get max numberprice for person always.

 

declare @IdPer int
set @IdPer = 1
SELECT NumId,STUFF((SELECT ',' + NAME FROM
 (
           SELECT c.numberprice as NumId,LTRIM(RTRIM(f.NAME))NAME
          FROM fruits f, cash c
          WHERE f.ID IN (SELECT CAST(item AS INTEGER) FROM dbo.SplitString(c.idfruit, ','))
          AND c.IdPerson = @IdPer
 ) as childTable
 WHERE  mainTable.NumId = childTable.NumId 
 FOR XML PATH('')), 1, 1, ' ') AS Name,
 customername,
 pname,
 NumId,
  REPLACE(SUM(CAST(REPLACE(Price, ',','.') AS DECIMAL(18,2))),'.',',') AS Price
FROM
(
   SELECT c.numberprice as NumId,p.pname,c.customername,c.numberprice,f.price
   FROM cash c join person p on c.idperson = p.id, fruits f
   where c.numberprice = (select MAX(numberprice) from cash where idperson = @IdPer)
   and c.idperson = @IdPer
)as mainTable
group by maintable.pname,maintable.customername,maintable.NumId,maintable.Price

Output result for idperson = 1, because max numberprice for it is 3:

NumId

Name

customername

pname

NumId

Price

3

apple,onion

Suhrob  

Rustam        

3

11,00

You are viewing reply posted by: dharmendr 2 months ago.
Posted 2 months ago

Hi PRA,

Refer below query.

DECLARE @fruits AS TABLE (Id INT, name VARCHAR(10), price INT)
INSERT INTO @Fruits VALUES (1,'apple',5)
INSERT INTO @Fruits VALUES (2,'orange',4)
INSERT INTO @fruits VALUES (3,'onion',6)
INSERT INTO @Fruits VALUES (4,'tomato',9)
 
DECLARE @Person AS TABLE (Id INT, pname VARCHAR(10))
INSERT INTO @Person VALUES(1,'Rustam')
INSERT INTO @Person VALUES(2,'Firuz')
INSERT INTO @Person VALUES(3,'Nurullo')
 
DECLARE @Cash AS TABLE (Id INT,idperson int ,customername VARCHAR(10),idfruit VARCHAR(10), numberprice INT)
INSERT INTO @Cash VALUES(1,1,'Suhrob','1,3',1)
INSERT INTO @Cash VALUES(2,3,'Amonullo','2,3',2)
INSERT INTO @Cash VALUES(3,1,'Suhrob','3,1',3)
INSERT INTO @Cash VALUES(4,2,'Alijon','1,3',4)
INSERT INTO @Cash VALUES(6,2,'Amonullo','2,1,4',6)
 
DECLARE @IdPer INT
SET @IdPer = 2
SELECT NumId,
STUFF((SELECT ',' + NAME
        FROM(
            SELECT c.numberprice NumId,LTRIM(RTRIM(f.NAME))NAME
            FROM @fruits f, @cash c
            WHERE f.ID IN (SELECT CAST(item AS INTEGER) FROM dbo.SplitString(c.idfruit, ','))
            AND c.IdPerson = @IdPer
            ) as childTable
        WHERE  mainTable.NumId = childTable.NumId
        FOR XML PATH('')), 1, 1, ' ') AS Name,
customername,
pname,
(SELECT SUM(f.price) 
FROM (
	SELECT CAST(Item AS INTEGER) Item 
	FROM dbo.SplitString((SELECT TOP 1 idfruit 
						FROM @Cash 
						WHERE idperson = @IdPer 
						ORDER BY numberprice DESC), ',')
)t
INNER JOIN @fruits f ON f.Id = t.Item) Price
FROM(
    SELECT c.numberprice NumId,p.pname,c.customername,c.numberprice,f.price
    FROM @cash c  
	JOIN @person p ON c.idperson = p.id, @fruits f
    WHERE c.numberprice = (SELECT MAX(numberprice) FROM @cash WHERE idperson = @IdPer)
    and c.idperson = @IdPer
    )AS mainTable
GROUP BY maintable.pname,maintable.customername,maintable.NumId

Output

NumId Name customername pname Price
6 apple,orange,tomato Amonullo Firuz 18