Is there is way to get SUM() of column without GROUPING by joining multiple tables in SQL Server

Mohammad Bastan 0
Viewed 38

I am getting SUM() of amount in CrowdfundedUser table by GROUP BY CrowdfundID but difficult to get SUM() because all columns are unique.

Crowdfund:

CrowdfundID GoalAmount StartedDate
9 10000 09/02/2022
5 20000 10/02/2022
55 350000 11/02/2022
444 541256 12/02/2022
54 78458 13/02/2022

CrowdfundedUser:

ID User ID CrowdfundID Amount
744 12214 9 1000
745 4124 5 8422
746 12214 55 784
747 12214 444 874
748 64554 54 652

CrowdfundiPaymentTransaction:

CrowdfundedUserID Invoice Amount PaymentDate
744 RA45A14124 1000 09/02/2022
745 RA45A12412 8422 10/02/2022
746 RA45U14789 784 11/02/2022
747 RA45F12457 874 12/02/2022
748 RA45M00124 652 13/02/2022

My query :

SELECT
    c.CrowdfundID,
    SUM(cu.Amount),
    SUM(cpt..Amount)
FROM 
    Crowdfund c
INNER JOIN 
    CrowdfundedUser cu ON c.CrowdfundID = cu.CrowdfundID
INNER JOIN 
    CrowdfundiPaymentTransaction cpt ON cu.ID = cpt.CrowdfundedUserID
GROUP BY 
    c.CrowdfundID
sql-server-2008 sql-server sql
1 Answers
SELECT c.CrowdfundID,    
SUM(cu.Amount) OVER (    
ORDER BY c.CrowdfundID) Amount,    
SUM(cpt..Amount) OVER (
ORDER BY c.CrowdfundID)    CptAmount    
FROM Crowdfund c    
INNER JOIN CrowdfundedUser cu ON c.CrowdfundID = cu.CrowdfundID    
INNER JOIN CrowdfundiPaymentTransaction cpt ON cu.ID = cpt.CrowdfundedUserID
Atul Pathrikar0
Related