Deep Live Cam Local Installation Easy Guide for Face Swap and Deepfake Video on Webcam

Need to calculate different aggregates based on conditions in a single query — without multiple subqueries or CASE statements? Oracle AI Database 26ai introduces **Aggregation Filters**, a clean and powerful new feature that makes conditional SUM, COUNT, AVG, and other aggregates much simpler.

What Are Aggregation Filters?

Aggregation filters let you apply a WHERE condition directly inside an aggregate function. Only rows that match the condition are included in that specific calculation.

Syntax

aggregate_function ( expression ) FILTER ( WHERE condition )

Works with any aggregate function: SUM, COUNT, AVG, MAX, MIN, etc.

Practical Examples

Example 1: Count Only Active Records

SELECT COUNT(*) FILTER (WHERE status = 'ACTIVE') AS active_count
FROM employees;

Example 2: Sum Salaries for a Specific Department

SELECT SUM(salary) FILTER (WHERE department = 'SALES') AS sales_total
FROM employees;

Example 3: Multiple Conditional Counts in One Query

SELECT
    COUNT(*) FILTER (WHERE status = 'ACTIVE')   AS active_count,
    COUNT(*) FILTER (WHERE status = 'INACTIVE') AS inactive_count
FROM employees;

Example 4: Quarterly Sales Breakdown in One Pass

SELECT
    year,
    SUM(sales)                                   AS year_sales,
    SUM(sales) FILTER (WHERE qtr_num IN (1, 2)) AS q1q2_sales,
    SUM(sales) FILTER (WHERE qtr_num IN (3, 4)) AS q3q4_sales
FROM sales_fact f
LEFT OUTER JOIN time_dim t ON (f.time_id = t.month_id)
GROUP BY year
ORDER BY year;

Key Notes

• Aggregation filters are evaluated after the main WHERE clause of the query.
• They provide a much cleaner alternative to writing separate subqueries or complex CASE expressions.
• You can combine multiple filtered aggregates in the same SELECT list.

Best Practices

• Use aggregation filters whenever you need different conditional totals in the same result set
• They are especially powerful for reporting, dashboards, and analytics queries
• Combine with GROUP BY for even more flexible breakdowns
• Great for simplifying queries that previously required multiple CTEs or subqueries

Conclusion

Aggregation filters are a small but incredibly useful enhancement in Oracle AI Database 26ai. They make your SQL cleaner, more readable, and more performant by eliminating unnecessary subqueries and complex logic.

Whether you’re building reports, dashboards, or analytical applications, aggregation filters will quickly become one of your favorite new SQL features.