CODEWITHSUNDEEP
sql-server
John Doe
John Doe

Reputation: 15

SQL Server Sum Returning Invalid Number

I am using LEFT JOIN three times in a query to display user information. For some reason, the second last LEFT JOIN affects the output of the SUM function.

I have tried removing the second last LEFT JOIN statement which returns the correct value. I don't see why it would change the value.

SELECT 
    [tbl_users].[id],
    [username],
    COUNT([tbl_password_resets].[id]) as passwordresets,
    SUM(CASE WHEN [tbl_files].[user_id] = [tbl_users].[id] THEN 1 ELSE 0 END) as uploads,
    COUNT([tbl_downloads].[id]) as downloads,
    CAST(SUM(CASE WHEN [tbl_downloads].[liked] = 1 OR [tbl_downloads].[disliked] = 1 THEN 1 ELSE 0 END) AS FLOAT) / NULLIF(COUNT([tbl_downloads].[id]), 0) as ratio,
    [ban]
FROM 
    [tbl_users]
LEFT JOIN 
    [tbl_password_resets] ON [tbl_users].[id] = [tbl_password_resets].[user_id]
LEFT JOIN 
    [tbl_downloads] ON [tbl_users].[id] = [tbl_downloads].[user_id]
LEFT JOIN 
    [tbl_files] ON [tbl_files].[user_id] = [tbl_users].[id]
GROUP BY 
    [tbl_users].[id], [tbl_users].[username], [tbl_users].[ban]

The result for uploads is 9 instead of 3.

Upvotes: 0

Views: 234

Answers (1)

Dávid Laczkó
Dávid Laczkó

Reputation: 1101

If any joined table is duplicating rows, that affects the result of COUNTs and SUMs. Comment out the aggregates and the GROUP BY and the JOINs for testing and see what happens with the row count when you build the query adding the JOINs one-by-one. The more rows fall into one group, the more values will be COUNTed and SUMmed.

Upvotes: 1

Related Questions