The author discusses a mysterious SQL query that has been causing confusion among users. The query, which involves summing values from different tables, produces unexpected results that deviate from the SQL standard. The author delves into the SQL standard to understand why this behavior occurs and analyzes various SQL implementations to see which ones adhere to the standard. Only a few systems follow the standard correctly, while others have modified the behavior over time. The author also discovers that SQL Server has additional limitations regarding outer references in aggregates. Ultimately, the author ponders the reason behind the introduction of this confusing behavior.
https://modern-sql.com/blog/2023-12/curious-case-aggregation-query