CODEWITHSUNDEEP
sqlsql-server
techora
techora

Reputation: 619

Adding a second SUM to current query

I am attempting to add in another column to show declined totals. I want to add in a Declined column that goes out and does this SUM(D.FulfillmentAmt) as Declined WHERE D.StatusID = '2'. At the moment I'm using two queries but want to combine them into one using the one below as a starting point:

SELECT COALESCE(SUM(D.FulfillmentAmt),0) as Approved, DB.Budget, DC.CommunityName 
FROM DCommunity DC
LEFT JOIN DBudget DB ON DC.CID  = DB.Community AND DB.[Year] = year(getdate())
LEFT JOIN Donations D ON D.Market = DC.CID AND D.StatusId = '1'
GROUP BY DC.CommunityName, DB.Budget
ORDER BY DC.CommunityName

My current query displays the following results:

Approved | Budget | CommunityName
100      | 2000   | City1
250      | 3000   | City2
1330     | 5000   | City3
50       | 2000   | City4
0        | 3000   | City5
2500     | 7000   | City6

I want it to function like this:

Approved | Declined | Budget | CommunityName
100      | 100      | 2000   | City1
250      | 0        | 3000   | City2
1330     | 452      | 5000   | City3
50       | 863      | 2000   | City4
0        | 0        | 3000   | City5
2500     | 105      | 7000   | City6

I need to make sure it still shows all the cities regardless if they have records in Donations table. For example, City5 doesn't have any records but still shows up as 0. I want to keep it like that.

Upvotes: 2

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You want to use conditional aggregation:

SELECT SUM(CASE WHEN status = '1' THEN D.FulfillmentAmt ELSE 0 END) as Approved,
       DB.Budget, DC.CommunityName,
       SUM(CASE WHEN status = '2' THEN D.FulfillmentAmt ELSE 0 END) as DeclinedAmount
. . .

And then use D.StatusId IN ('1', '2') later in the query.

Upvotes: 3

Related Questions