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

debugcn Published at Dev

bajivali shaik

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?

Max Sorin

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

Collected from the Internet

Please contact [email protected] to delete if infringement.