CODEWITHSUNDEEP
sqlsql-servert-sql
August Williams
August Williams

Reputation: 929

How to perform group function over an outer join query?

I have the following categories:

category_id                             name
-----                                   -----
50D34E5A-A935-490A-9492-153DE50A94A2    Luxuries
013E3D0F-E755-495B-8D1E-4A3D1340ACF8    Household
88C477EE-CF99-49B4-9E92-4C41B09A5715    Petrol
40099E3A-18F1-4710-A803-7107648518CC    Other
E3B81693-07B5-4D69-A3EC-796CA4290B45    Rent
F0728052-0733-454B-B8EE-96AB6D6E40BE    Insurance
6E06581A-1643-4DEC-90B7-9D57F770F313    Groceries
CFD1ED67-7059-4A33-8DD6-F2FFAB213970    Monthly Bill

And the following transactions (shortened and joined on category_id):

category_id    amount
------         -----
Luxuries       14
Household      14
Petrol         14
Other          14
Rent           14
Insurance      14

There are no Groceries transactions. I would like to sum these amounts and display their count but include Groceries in the results, but displaying zero. I have tried this:

SELECT SUM(ut.amount), COUNT(ut.amount), c.name
FROM User_Transaction ut
FULL OUTER JOIN Category c ON (ut.category_id = c.category_id)
GROUP BY c.name



total   count   name
---     ---     ---
84      6       Household
84      6       Insurance
84      6       Luxuries
98      7       Monthly Bill
56      4       Other
182     13      Petrol
112     8       Rent

But Groceries has not been included. How can I include Groceries on the result set but just displaying as 0?

Upvotes: 0

Views: 22

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use a LEFT JOIN starting with category (that values you want to keep):

SELECT c.name, COALESCE(SUM(ut.amount), 0) as amount,
       COUNT(ut.category_id) as num_transactions, 
FROM Category c LEFT JOIN
     User_Transaction ut
     ON ut.category_id = c.category_id
GROUP BY c.category_id, c.name;

That said, I think your query should do what you want, although it is misleading to use a full join in this context.

Upvotes: 1

Related Questions