Filter the groups

Count the departments in which the average age of the employees is over 30, or that have more than three employees who are over 40 years old.

SQL

SELECT count(*)
FROM (SELECT dept FROM employee GROUP BY dept
     HAVING avg(age)>30 OR
           sum( CASE WHEN age>40 THEN 1 ELSE 0 END )>3)

The intention of using GROUP BY clause is to divide the source result set to several individual subsets, but SQL cannot store the subsets. Instead, it can’t be helped that it calculates the summary, converting the filter and count on the subsets to the conditional summary. And a nested subquery must be used to sum the result set again.

SPL

A
1 =demo.query("select * from employee").group(dept)
2 =A1.count(~.avg(age)>30 || ~.count(age>40)>3)

SPL can retain the grouping result, that is the subsets of a result set, so it calculates in compliance with the user's natural logical mental process.

In order to simplify the SQL statement as much as possible in the examples, the window functions of SQL 2003 standard are widely used, and accordingly the Oracle database syntax which has best support for SQL 2003 is adopted in this essay.