【Question】

I have a table emp:

I wanted the output in the below format: Please help me.

【Answer】

You want to count age values according to three listed intervals. With fixed intervals, we can write the aggregations in one SQL query:

SELECT deptno, NVL (SUM (CASE WHEN age BETWEEN 0 AND 20 THEN 1 END),0)"Age <=20",NVL (SUM (CASE WHEN age BETWEEN 21 AND 40 THEN 1 END),0)"Age <=40",NVL (SUM (CASE WHEN age BETWEEN 41 AND 60 THEN 1 END),0) "Age <=60" FROM emp GROUP BY deptno;

But with dynamic intervals, like “Age<=20,Age<=40,Age<=60” and “Age<=20&Salary>3000, Age<=50”, it’s hard to aggregate simply in SQL. We often turn to Java or reporting tools to generate the SQL query. The workarounds are still complicated.

SPL supports result sets with dynamic columns and can pass the interval conditions as parameter values. It’s easy to solve your problem in esProc SPL. Suppose the conditional parameters are [“Age<=20”,“Age>20 && Age<=40”, “Age>40 && Age<=60”], we can write the following code:

A1: It is a sequence of conditions;

A2: Connect to the database;

A3: Retrieve data from emp table;

A4: Rewrite each member of A1’s sequence of conditions in the format of count(condition):condition. The part before semicolon is the expression performing a count operation by the condition, the part after it is the resulting field name;

A5: Query data for the fields defined in A4 group by group by Deptno. concat() function concatenates members in A1’s sequence with delimiters and returns result as a string. @c option means concatenating them with comma. ${} is a macro replacement which converts the concatenated string into expressions that can be identified by groups() function. For example, count(Age<=20):Age<=20 means counting by condition Age<=20 and the resulting field name is Age<=20. Functions that such a macro replacement applies include sum() and so on.

A1

A3

A4

A5