如何在MariaDB中筛选数据并对筛选后的数据计算平均值？

Got it, you’re already halfway there with your grouped count query! To get the average of those daily counts, you just need to wrap your existing subquery in an outer AVG() aggregation.

Here's the modified SQL that does exactly what you need:

SELECT AVG(counts) AS avg_daily_active_warrants
FROM (
    SELECT COUNT(ch_id) AS counts
    FROM tbl_warrants_checked
    WHERE status = "active"
    GROUP BY dateChecked
) AS daily_counts;

Let me break this down for clarity:

• Inner subquery: This part stays almost identical to your original code. It filters for rows where status is "active", groups them by dateChecked, and counts how many ch_id entries exist per day (aliased as counts).
• Outer query: We take that set of daily counts and calculate the average using AVG(counts), and alias the result as avg_daily_active_warrants so it’s clearly labeled in your output.

If you want to round the average to a specific number of decimal places (e.g., 2), you can use the ROUND() function like this:

SELECT ROUND(AVG(counts), 2) AS avg_daily_active_warrants
FROM (
    SELECT COUNT(ch_id) AS counts
    FROM tbl_warrants_checked
    WHERE status = "active"
    GROUP BY dateChecked
) AS daily_counts;

That’s it! This will give you the average number of active warrants per day based on your grouped statistics.

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