SUM CASE LEFT JOIN output gives wrong sum

Question

I have multiple left joins that i need to aggregate and denormalize data As shown is this example i get SUM of 3 FA instead 1 FA by F.

example: https://rextester.com/FOO264444

SELECT 
SUM(CASE WHEN fa.PFId=1111 and fa.AcId=2 THEN 1 ELSE 0 END) as conditionalcountall

FROM #Fan f
LEFT JOIN #PF pf
    ON pf.FId = f.Id
LEFT JOIN #LPe lip
    ON lip.Id = pf.PId
LEFT JOIN #FA fa
    ON fa.PFId = pf.Id
LEFT JOIN #Ac a
    ON a.Id = fa.AcId
LEFT JOIN #DelAc das
    ON das.PFId = pf.Id
LEFT JOIN #DAc da
    ON da.Id = das.DAId

-------------------------   
id |conditionalcountall |
-------------------------
  1          3          |
-------------------------

When i remove the the last 2 Left Joins it gives the desired output of 1. But i need these 2 joins to aggregate other data from those 2 tables.

SELECT 
    SUM(CASE WHEN fa.ProfileFanId=1111 and fa.ActivityId=2 THEN 1 ELSE 0 END) as conditionalcountall

    FROM #Fan f
    LEFT JOIN #ProfileFan pf
        ON pf.FanId = f.Id
    LEFT JOIN #LinkedInProfile lip
        ON lip.Id = pf.ProfileId
    LEFT JOIN #FanActivity fa
        ON fa.ProfileFanId = pf.Id
    LEFT JOIN #Activity a
        ON a.Id = fa.ActivityId

id |conditionalcountall |
-------------------------
 1 |             1      |
-------------------------

What am I doing wrong?

This is a common error where people want the join of some aggregations (each possibly involving joining) but they erroneously try to do all the joining then all the aggregating. This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, Apr 10 '19 at 16:27
Why you are using all the tables in joins when you just need sum on two fields which are coming from FanActivity table? Your data are redundant that's why it's returning the correct sum of 3 — Adnan Ahmed Ansari, Apr 10 '19 at 16:39
It's good you give [mcve] code but please put it as text in your question, not just a link--make your post self-contained. To make it "minimal" include the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) You need to "to aggregate other data" but you do not "need these 2 joins"--it gives you a table that is not useful. If you looked at your partial result you might see that. Your MCVE doesn't actually tell us what rows you want in your result. Plus, writing it out might lead to you seeing that you need subqueries. — philipxy, Apr 10 '19 at 16:39
Possible duplicate of [Strange duplicate behavior from GROUP\_CONCAT of two LEFT JOINs of GROUP\_BYs](https://stackoverflow.com/questions/45250646/strange-duplicate-behavior-from-group-concat-of-two-left-joins-of-group-bys) — philipxy, Apr 10 '19 at 16:52

score 1 · Answer 1 · edited Feb 05 '20 at 20:07

You can get your desired result with the help of different logics. For your given scenario you can achieve this by the change in your case when logic

SELECT  COUNT(DISTINCT 
             CASE WHEN fa.PFId=1111 and fa.AId=2 
                  THEN CAST(fa.PFId AS VARCHAR) + '-' + CAST(fa.ActivityId AS VARCHAR) 
             ELSE NULL END
        ) as conditionalcountall

In this logic CASE WHEN set a string with a combination of your desired field and when the condition bypass set a null because we know that count clause doesn't count null field and with the help of distinct you get 1 instead of 3

score 0 · Answer 2 · edited Feb 04 '20 at 12:27

#DeliveryActions and #DA must have multiple rows in them on the join. If you only care to find out if it exists once, you have to group the data in those tables.

--Replace THIS

LEFT JOIN #DA das
    ON das.PFId = pf.Id
LEFT JOIN #DA da
    ON da.Id = das.DAId

--WITH THIS

LEFT JOIN (SELECT PFId
            FROM #DA ia
            LEFT JOIN #DA ib
            ON ia.DAId = ib.Id
            GROUP BY PFId) das
            ON das.PFId = pf.Id

Note that because you're using LEFT JOIN everywhere, there could be 0 rows in any of the other tables though? Not sure if that's what you were after. If you need the count where it exists in all tables just change the LEFT JOIN to INNER JOIN (or JOIN if lazy like me)

OK so the last 2 joins need to be grouped as one subquery with a single ID. I'll edit. — Richard, Apr 11 '19 at 14:51

SUM CASE LEFT JOIN output gives wrong sum

2 Answers2