Grouping rows by the specified order

List the number of female employees and their average age in each of the following departments: R&D, Sales, Marketing, and Administration.

SQL

WITH A AS (SELECT 1 seq_no., 'R&D' dept FROM dual UNION
        SELECT 2, 'sales'  FROM dual UNION
        SELECT 3, 'marketing'  FROM dual UNION
        SELECT 4, 'admin'  FROM dual )
SELECT A.dept,count(*) employee_total,avg(B.age) average_age
FROM A left join employee B on (A.dept=B.dept)
WHERE B.gender='female' GROUP BY A.seq_no.,A.dept
ORDER BY A.seq_no.

As the SQL record sets are in random order, it could be a headache to sort and group them in the specified order, the user will need to manually insert a sequence number field to ensure the correctness of the order for record sets.

SPL

A
1 =["R&D","sales","marketing","admin"]
2 =demo.query("select * from employee").select(gender:"female").align@a(A1,dept)
3 =A2.new(A1(#):dept,~.len():employee_total,~.avg(age):average_age)

With ordered set and the ad hoc align function, SPL handles this type of computations with great ease.

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.