CODEWITHSUNDEEP
sqlsql-server
Ben
Ben

Reputation: 609

Aggregate functions with more than one join

I have this simple example to count how many quotes per account I have

SELECT 
A.accountId, 
SUM(CASE WHEN Q.QuoteId IS NULL THEN 0 ELSE 1 END) as NumberOfQuotes
FROM Account A 
LEFT JOIN Quote Q ON A.AccountId = Q.AccountId
WHERE A.accountid = '58B3F89E-E1E5-E511-B1F5-4ED0FE97D338'
GROUP BY A.AccountId

This gives 9 which is correct.

If I then want to show how many quotes and how many orders an account has I tried this:

SELECT 
A.accountId, 
SUM(CASE WHEN Q.QuoteId IS NULL THEN 0 ELSE 1 END) as NumberOfQuotes,
SUM(CASE WHEN P.new_propid IS NULL THEN 0 ELSE 1 END) as NumberOfOrders
FROM Account A 
LEFT JOIN Quote Q ON A.AccountId = Q.AccountId
LEFT JOIN new_prop P ON A.AccountId = P.New_accountid
WHERE A.accountid = '58B3F89E-E1E5-E511-B1F5-4ED0FE97D338'
GROUP BY A.AccountId

This gives 18 for both figures, which is wrong.

Obviously i'm doing this wrong. Is it possible to do multiple aggregates/joins like this?

Thanks

Upvotes: 0

Views: 41

Answers (1)

Thom A
Thom A

Reputation: 95544

You could use DISTINCT instead:

SELECT A.accountId,
       COUNT(DISTINCT Q.QuoteId) AS NumberOfQuotes,
       COUNT(DISTINCT P.new_propid) AS NumberOfOrders
FROM Account A
     LEFT JOIN Quote Q ON A.AccountId = Q.AccountId
     LEFT JOIN new_prop P ON A.AccountId = P.New_accountid
WHERE A.accountid = '58B3F89E-E1E5-E511-B1F5-4ED0FE97D338'
GROUP BY A.AccountId;

Also, note you don't need to use a CASE expression with a SUM along the lines of SUM(CASE WHEN Q.QuoteId IS NULL THEN 0 ELSE 1 END). Aggregate expressions automatically ignore NULL values; unless you have SET ANSI_NULLS OFF, but that's highly unrecommended:

SET ANSI_NULLS (Transact-SQL)

In a future version of SQL Server, ANSI_NULLS will be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Upvotes: 2

Related Questions