基于年龄分段分组统计各厂商消费总量的SQL语句编写求助

Let's get your query sorted to match the expected output you shared. Here's the breakdown of what needs fixing and the final working SQL:

Key Issues in Your Initial Query #

• You were selecting the raw age instead of the age band from your CASE statement
• Your GROUP BY clause only included ageband, but you need to group by both the age band and manufacturer to get totals per manufacturer within each age group
• Using explicit JOIN syntax instead of comma-separated tables makes the query easier to read and maintain

Corrected SQL Query #

SELECT
    -- Use your CASE statement to define age bands, fixed the typo in the 55-64 range
    CASE
        WHEN age >=15 AND age <=24 THEN '15-24'
        WHEN age >=25 AND age <=34 THEN '25-34'
        WHEN age >=35 AND age <=44 THEN '35-44'
        WHEN age >=45 AND age <=54 THEN '45-54'
        WHEN age >=55 AND age <=64 THEN '55-64' -- Fixed the typo here (was 54-64 with upper limit 66)
        WHEN age >=65 THEN '65+'
    END AS `Age Category`,
    manu.manufacturer AS `Manufacturer`,
    SUM(consump.volume) AS `Total Volume Consumed`
FROM indi
INNER JOIN consump ON indi.id = consump.id
INNER JOIN manu ON consump.code = manu.code
-- Optional: Filter out any users without age data if needed
WHERE indi.age IS NOT NULL
GROUP BY `Age Category`, manu.manufacturer
ORDER BY `Age Category`, manu.manufacturer;

Explanation #

1. Age Band Calculation: The CASE statement is embedded directly in the SELECT clause to generate the Age Category column you need. I fixed a small typo where you had 54-64 for the 55-66 range (adjusted to 55-64 with upper limit 64 to keep the ranges consistent).
2. Table Joins: Using INNER JOIN explicitly clarifies how the tables are linked, which is better practice than the old comma-separated syntax.
3. Grouping: We group by both Age Category and Manufacturer to ensure we get the total volume consumed for each manufacturer within every age segment.
4. Ordering: The ORDER BY clause ensures the results are sorted neatly by age category and manufacturer, matching your expected output structure.

Notes #

• If you want to include age categories that have no consumption data (showing 0 volume), you'd need to use LEFT JOIN instead of INNER JOIN and handle NULL sums with COALESCE(SUM(consump.volume), 0).
• Make sure the volume column in consump is a numeric type (like INT or DECIMAL) so SUM() works correctly.

内容的提问来源于stack exchange，提问作者ManicWomanIRL