Objective

Write a SQL query to calculate the sum of odd-valued and even-valued measurements separately for each day.

Table Schema:

• sensor_measurements

Task Requirements:

• Classify each measurement as odd or even based on its value
• Calculate the sum of odd-valued measurements for each day (odd_sum)
• Calculate the sum of even-valued measurements for each day (even_sum)
• Order results by measurement day

Output columns: measurement_day, odd_sum, even_sum

SELECT
    measurement_day,
    SUM(CASE WHEN measurement_value % 2 != 0 THEN measurement_value ELSE 0 END) AS odd_sum,
    SUM(CASE WHEN measurement_value % 2 = 0 THEN measurement_value ELSE 0 END) AS even_sum
FROM sensor_measurements
GROUP BY measurement_day
ORDER BY measurement_day;

Explanation

Step 1: Identifying Odd and Even Values

measurement_value % 2 != 0  -- odd
measurement_value % 2 = 0   -- even

The modulo operator % returns the remainder when dividing by 2. If the remainder is 0, the value is even. If it's not 0, the value is odd. This is the standard way to classify integers as odd or even in SQL.

Step 2: Using Conditional SUM with CASE

SUM(CASE WHEN measurement_value % 2 != 0 THEN measurement_value ELSE 0 END) AS odd_sum
SUM(CASE WHEN measurement_value % 2 = 0 THEN measurement_value ELSE 0 END) AS even_sum

A CASE expression inside SUM acts as a conditional aggregator. For odd_sum, only odd values contribute to the total - even values are replaced with 0 via the ELSE clause. The same logic applies in reverse for even_sum. This lets us compute both sums in a single pass over the data.

Step 3: Grouping by Day

GROUP BY measurement_day

GROUP BY partitions the data by measurement_day, so the conditional SUM expressions calculate separate totals for each day independently.

Step 4: Handling Days with Missing Types

ELSE 0

The ELSE 0 clause is essential. If a day has only odd measurements, the even_sum would otherwise aggregate nothing. Using ELSE 0 ensures these cases return 0 rather than NULL, giving clean and predictable output.

Step 5: Sorting the Results

ORDER BY measurement_day

The output is sorted chronologically by measurement_day in ascending order.