CODEWITHSUNDEEP
sql-server
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

how to use sum and max in one query

I have a table 

tblPay
(
    CID bigint,
    PartyIdID bigint,
    PartyName varchar(50),
    AgentName varchar(50), 
    Agent bigint,
    Amount decimal(18,2),
    RecAmount decimal(18,2),
    OutStanding decimal(18,2)
)

I want to select sum of Amount,RecAmount group by PartyId and I also want to select Last Outstanding entry of each PartyID. For this i use following query.

SELECT  DISTINCT CID, 
        Party.AccLedger_ID PartyID, 
        Party.AccLedger_Name PartyName,
        Agent.AccLedger_Name AgentName,
        Agent.AccLedger_ID AgentID,
        sum(S.Amount) Amount,
        Sum(S.RecAmount) RecAmount,
        S.OutStanding 
Group by PartyID,
         Cid,
         Party.AccLedger_ID,
         Party.AccLedger_Name,
         Agent.AccLedger_Name,
         Agent.AccLedger_ID,
         S.OutStanding

But i am unable to achieve the sum of Amount,RecAmount and Last Outstanding Record of each Party. Can someone help me here.

enter image description here

This is the Answer i got.

Upvotes: 0

Views: 67

Answers (2)

3N1GM4
3N1GM4

Reputation: 3351

Assuming your records are inserted in tblPay.CID order, use a subquery

SELECT  p.PartyID,
        p.PartyName,
        SUM(p.Amount) AS PartyAmount,
        SUM(p.RecAmount) AS PartyRecAmount,
        (SELECT TOP 1 OutStanding FROM tblPay p2 WHERE p.PartyID = p2.PartyId ORDER BY CID DESC) AS LastOutStanding
FROM tblPay p
GROUP BY p.PartyID, p.PartyName

or an OUTER APPLY:

SELECT  p.PartyID,
        p.PartyName,
        SUM(p.Amount) AS PartyAmount,
        SUM(p.RecAmount) AS PartyRecAmount,
        lastRecord.OutStanding AS LastOutStanding
FROM tblPay p
OUTER APPLY
(
    SELECT TOP 1 OutStanding
    FROM tblPay p2
    WHERE p.PartyID = p2.PartyId
    ORDER BY CID DESC
) lastRecord
GROUP BY p.PartyID, p.PartyName

or, as JamieD77 suggests in his answer, use a CTE.

Upvotes: 2

JamieD77
JamieD77

Reputation: 13949

use a cte to get your tblPay information and join that to your other tables on the last record 

WITH cte AS (
    SELECT 
        CID,
        PartyName,
        SUM(Amount) OVER (PARTION BY PartyIdID) Amount,
        SUM(RecAmount) OVER (PARTITION BY PartyIdID) RecAmount,
        OutStanding,
        -- only assuming your CID determines order since you have no date?
        ROW_NUMBER() OVER (PARTITION BY PartyIdID ORDER BY CID DESC) Rn 
    FROM tblPay
) 
SELECT  Party.*,
        Agent.*,
        p.CID,
        p.PartyName,
        p.Amount,
        p.RecAmount,
        p.Oustanding
FROM    Party JOIN Agent
        JOIN cte p ON p.PartyIdID = Party.AccLedger_ID AND p.Rn = 1

Upvotes: 2

Related Questions