在CASE语句中实现Count()与Avg()统计的技术问题咨询

Hey there! Let's get to the bottom of why your CASE statement isn't producing the age-group stats you expect for total visits and average duration. Since you haven't shared your current code, I'll walk through common pitfalls and a couple of solid implementation patterns that should work for most SQL databases.

Common Mistakes to Check First #

Before diving into examples, here are the most frequent issues that throw off these kinds of aggregations:

• Missing or incorrect GROUP BY: If you forget to group by your age-group CASE expression, you'll end up with a single row of totals instead of per-group stats.
• Overlapping CASE conditions: For example, having WHEN age <= 18 and WHEN age >= 18 will double-count 18-year-olds (or cause unpredictable grouping depending on your database).
• Ignoring NULL values: If your duration column has NULLs, AVG() will automatically exclude them—if you want to treat NULLs as 0, use AVG(COALESCE(duration, 0)) instead.
• Using the wrong aggregation scope: If you're trying to get per-group stats but accidentally aggregating across all rows first, your numbers will be off.

Correct Implementation: Per-Group Rows #

If you want each age group as a separate row (the most common format), use this pattern:

SELECT
  -- Define your age groups clearly
  CASE
    WHEN age < 18 THEN 'Under 18'
    WHEN age BETWEEN 18 AND 30 THEN '18-30'
    WHEN age BETWEEN 31 AND 50 THEN '31-50'
    ELSE 'Over 50' -- Catch-all for any age outside the above ranges
  END AS age_group,
  COUNT(*) AS total_visits, -- Counts all visits in the group
  AVG(duration) AS avg_visit_duration -- Averages duration for the group
FROM visits -- Replace with your actual table name
GROUP BY
  -- Match the CASE expression exactly (or use the alias if your DB supports it)
  CASE
    WHEN age < 18 THEN 'Under 18'
    WHEN age BETWEEN 18 AND 30 THEN '18-30'
    WHEN age BETWEEN 31 AND 50 THEN '31-50'
    ELSE 'Over 50'
  END;

Note: Many modern databases (PostgreSQL, MySQL 8+, SQL Server) let you use the age_group alias directly in the GROUP BY clause to avoid repeating the CASE expression—simpler and less error-prone!

Alternative: Age Groups as Columns #

If you prefer all stats in a single row (each group as a column), use conditional aggregation:

SELECT
  COUNT(*) AS total_all_visits,
  -- Count visits per group
  COUNT(CASE WHEN age < 18 THEN 1 END) AS under_18_visits,
  COUNT(CASE WHEN age BETWEEN 18 AND 30 THEN 1 END) AS eighteen_to_thirty_visits,
  COUNT(CASE WHEN age BETWEEN 31 AND 50 THEN 1 END) AS thirtyone_to_fifty_visits,
  COUNT(CASE WHEN age > 50 THEN 1 END) AS over_fifty_visits,
  -- Average duration per group
  AVG(CASE WHEN age < 18 THEN duration END) AS under_18_avg_duration,
  AVG(CASE WHEN age BETWEEN 18 AND 30 THEN duration END) AS eighteen_to_thirty_avg_duration,
  AVG(CASE WHEN age BETWEEN 31 AND 50 THEN duration END) AS thirtyone_to_fifty_avg_duration,
  AVG(CASE WHEN age > 50 THEN duration END) AS over_fifty_avg_duration
FROM visits;

Next Steps #

If you're still getting unexpected results, share your current code and a sample of your desired output—this will help pinpoint exactly where things are going wrong. For example, maybe your age ranges are off, or you're grouping on the wrong field.

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