ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Query to get maximum SUM based on another column value in SQL Server<p>Hi <a class="username" rel="PRA">PRA</a>,</p> <p>Refer below query.</p> <pre class="brush: sql">DECLARE @fruits AS TABLE (Id INT, name VARCHAR(10), price INT) INSERT INTO @Fruits VALUES (1,&#39;apple&#39;,5) INSERT INTO @Fruits VALUES (2,&#39;orange&#39;,4) INSERT INTO @fruits VALUES (3,&#39;onion&#39;,6) INSERT INTO @Fruits VALUES (4,&#39;tomato&#39;,9) DECLARE @Person AS TABLE (Id INT, pname VARCHAR(10)) INSERT INTO @Person VALUES(1,&#39;Rustam&#39;) INSERT INTO @Person VALUES(2,&#39;Firuz&#39;) INSERT INTO @Person VALUES(3,&#39;Nurullo&#39;) DECLARE @Cash AS TABLE (Id INT,idperson int ,customername VARCHAR(10),idfruit VARCHAR(10), numberprice INT) INSERT INTO @Cash VALUES(1,1,&#39;Suhrob&#39;,&#39;1,3&#39;,1) INSERT INTO @Cash VALUES(2,3,&#39;Amonullo&#39;,&#39;2,3&#39;,2) INSERT INTO @Cash VALUES(3,1,&#39;Suhrob&#39;,&#39;3,1&#39;,3) INSERT INTO @Cash VALUES(4,2,&#39;Alijon&#39;,&#39;1,3&#39;,4) INSERT INTO @Cash VALUES(6,2,&#39;Amonullo&#39;,&#39;2,1,4&#39;,6) DECLARE @IdPer INT SET @IdPer = 2 SELECT NumId, STUFF((SELECT &#39;,&#39; + 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, &#39;,&#39;)) AND c.IdPerson = @IdPer ) as childTable WHERE mainTable.NumId = childTable.NumId FOR XML PATH(&#39;&#39;)), 1, 1, &#39; &#39;) 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), &#39;,&#39;) )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</pre> <p><strong><span style="text-decoration: underline;">Output</span></strong></p> <table border="1"> <tbody> <tr> <td><strong>NumId</strong></td> <td><strong>Name</strong></td> <td><strong>customername</strong></td> <td><strong>pname</strong></td> <td><strong>Price</strong></td> </tr> <tr> <td>6</td> <td>apple,orange,tomato</td> <td>Amonullo</td> <td>Firuz</td> <td>18</td> </tr> </tbody> </table>https://www.aspforums.net:443/Threads/160402/Query-to-get-maximum-SUM-based-on-another-column-value-in-SQL-Server/https://www.aspforums.net:443/Threads/160402/Query-to-get-maximum-SUM-based-on-another-column-value-in-SQL-Server/Thu, 11 Oct 2018 05:46:07 GMT