Understanding the Limitations of Scalar Subqueries: A Guide to Conditional Aggregation and Optimized Querying