[Solved] How would you modify this query to exclude products with zero sales from the percent of total calculation?SELECT product, amount, ROUND(amount * 100.... | SQL

SQL - Advanced Window Functions

How would you modify this query to exclude products with zero sales from the percent of total calculation?

SELECT product, amount, ROUND(amount * 100.0 / SUM(amount) OVER (), 2) AS pct_total FROM sales;

AAdd a WHERE clause: <code>WHERE amount > 0</code> before the SELECT

BAdd a FILTER clause inside SUM: <code>SUM(amount) FILTER (WHERE amount > 0) OVER ()</code>

CUse CASE inside SUM: <code>SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) OVER ()</code>

DAdd HAVING clause: <code>HAVING amount > 0</code>

Step-by-Step Solution

Solution:

Step 1: Understand filtering inside window functions
WHERE filters rows before window functions, excluding rows from output.
Step 2: Use CASE inside SUM to include zero sales rows but exclude zero amounts from sum
CASE allows conditional sum without removing rows.
Final Answer:
Use CASE inside SUM to exclude zero sales from total -> Option C
Quick Check:
CASE inside SUM filters values without filtering rows [OK]

Quick Trick: Use CASE inside SUM to filter values, keep all rows [OK]

Common Mistakes:

Master "Advanced Window Functions" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

How would you modify this query to exclude products with zero sales from the percent of total calculation?