CODEWITHSUNDEEP
sql-server
bajivali shaik
bajivali shaik

Reputation: 81

how to sum up a column value using the other column value

i have a stored procedure

ALTER  PROCEDURE [dbo].[PaymentServiceDetailBusinessUnit]
(
--@MRRId INT ,
@POId INT ,
@SDId INT 

)

AS
BEGIN

SELECT  BusinessUnit  , E1Description,Fund,SpendPriority,ServiceType,
 REUId , RCUId,  POId, PRR_Id, SUM (PaymentAmount) as TotalPaymentAmount
 FROM 
 (
SELECT  BU.BusinessUnit , BU.E1Description,BU.Fund,BU.SpendPriority,BU.ServiceType,
REU.Id AS REUId ,RCU.Id AS RCUId, PaymentAmount,PO.Id as POId, REU.PRR_Id
FROM [EBSTest].[dbo].[PaymentDetail] PD
     JOIN PurchaseOrder PO ON PO.Id = PD.PO_Id
     JOIN fBusinessUnit BU ON BU.id = PD.BU_Id
LEFT JOIN Reimbursement_EBSUtilization REU ON REU.Id = PD.REU_Id
LEFT JOIN Reimbursement_CDSUtilization RCU ON RCU.Id = PD.RCU_Id
LEFT JOIN ProviderReimbursementRequest PRR ON (PRR.Id = REU.PRR_Id  OR PRR.Id = RCU.PRR_Id)
LEFT JOIN CDSUtilization CDS ON CDS.Id = RCU.CDSU_Id


WHERE 
PO.Id = @POId  AND 
--PRR.MRR_Id = @MRRId AND 
(REU.SD_Id = @SDId OR CDS.ServiceDetail_Id  = @SDId)
) AS dt

GROUP BY 
BusinessUnit , E1Description,Fund,SpendPriority,ServiceType,REUId , RCUId, POId,PRR_Id



END

the out put is 

BusinessUnit    E1Description   Fund    SpendPriority   ServiceType REUId RCUId POId    PRR_Id  TotalPaymentAmount
10000002    MVBsd1hnhg  CF  Normal  SA  2331    NULL    12  934 100.00
10000002    MVBsd1hnhg  CF  Normal  SA  2346    NULL    12  935 60.00
10000002    MVBsd1hnhg  CF  Normal  SA  2361    NULL    12  937 40.00
10000002    MVBsd1hnhg  CF  Normal  SA  2389    NULL    12  941 40.00
10000002    MVBsd1hnhg  CF  Normal  SA  2406    NULL    12  943 40.00

but i want to show only one record with the total sum of total payment amount using the businessunit column -10000002 ie.

some thing like this

**10000002**    MVBsd1hnhg  CF  Normal  SA  2331    NULL    12  934 **280.00**

can any one help me on this?

Upvotes: 0

Views: 50

Answers (2)

Cato
Cato

Reputation: 3701

something like

;WITH CTE AS
(
        SELECT  BusinessUnit  , E1Description,Fund,SpendPriority,ServiceType,
         REUId , RCUId,  POId, PRR_Id, SUM (PaymentAmount) as TotalPaymentAmount
         FROM 
         (
        SELECT  BU.BusinessUnit , BU.E1Description,BU.Fund,BU.SpendPriority,BU.ServiceType,
        REU.Id AS REUId ,RCU.Id AS RCUId, PaymentAmount,PO.Id as POId, REU.PRR_Id
        FROM [EBSTest].[dbo].[PaymentDetail] PD
             JOIN PurchaseOrder PO ON PO.Id = PD.PO_Id
             JOIN fBusinessUnit BU ON BU.id = PD.BU_Id
        LEFT JOIN Reimbursement_EBSUtilization REU ON REU.Id = PD.REU_Id
        LEFT JOIN Reimbursement_CDSUtilization RCU ON RCU.Id = PD.RCU_Id
        LEFT JOIN ProviderReimbursementRequest PRR ON (PRR.Id = REU.PRR_Id  OR PRR.Id = RCU.PRR_Id)
        LEFT JOIN CDSUtilization CDS ON CDS.Id = RCU.CDSU_Id


        WHERE 
        PO.Id = @POId  AND 
        --PRR.MRR_Id = @MRRId AND 
        (REU.SD_Id = @SDId OR CDS.ServiceDetail_Id  = @SDId)
        ) AS dt

        GROUP BY 
        BusinessUnit , E1Description,Fund,SpendPriority,ServiceType,REUId , RCUId, POId,PRR_Id
),
CTE2 AS 
(
SELECT row_number() OVER(ORDER BY TotalPaymentAmount DESC) AS rown,* FROM CTE  WHERE rown = 1
)
SELECT *, (SELECT SUM(ST.TotalPaymentAmount) FROM CTE2 ST) As TheTotalAmount  FROM CTE2 WHERE CTE2.rown=1;

Upvotes: 0

Max Sorin
Max Sorin

Reputation: 1052

Remove REUId , RCUId, POId,PRR_Id from the Group By clause, as those appear to be causing the partitioning of the sum aggregation. 

SELECT  BusinessUnit  , E1Description,Fund,SpendPriority,ServiceType,
 SUM (PaymentAmount) as TotalPaymentAmount
 FROM 
 (
SELECT  BU.BusinessUnit , BU.E1Description,BU.Fund,BU.SpendPriority,BU.ServiceType,
REU.Id AS REUId ,RCU.Id AS RCUId, PaymentAmount,PO.Id as POId, REU.PRR_Id
FROM [EBSTest].[dbo].[PaymentDetail] PD
     JOIN PurchaseOrder PO ON PO.Id = PD.PO_Id
     JOIN fBusinessUnit BU ON BU.id = PD.BU_Id
LEFT JOIN Reimbursement_EBSUtilization REU ON REU.Id = PD.REU_Id
LEFT JOIN Reimbursement_CDSUtilization RCU ON RCU.Id = PD.RCU_Id
LEFT JOIN ProviderReimbursementRequest PRR ON (PRR.Id = REU.PRR_Id  OR PRR.Id = RCU.PRR_Id)
LEFT JOIN CDSUtilization CDS ON CDS.Id = RCU.CDSU_Id


WHERE 
PO.Id = @POId  AND 
--PRR.MRR_Id = @MRRId AND 
(REU.SD_Id = @SDId OR CDS.ServiceDetail_Id  = @SDId)
) AS dt

GROUP BY 
BusinessUnit , E1Description,Fund,SpendPriority,ServiceType

Upvotes: 2

Related Questions