# How SQL Groups Rows Every Three Ones

Data table EMPLOYEE stores employee data. Below is part of the table:

 EID NAME GENDER STATE HIREDATE SALARY 1 Rebecca F California 2005/3/11 7000 2 Ashley F New York 2008/3/16 11000 3 Rachel F New Mexico 2010/12/1 9000 4 Emily F Texas 2006/8/15 7000 5 Ashley F Texas 2004/7/30 16000 … … … … … …

We are trying to group the table every three rows according to hire date and calculate the average salary in each group.

That is, grouping employee records every three ones by HIREDATE, and calculating the average salary in each group.

SQL written in ORACLE:

SELECT GROUP_NO, avg(SALARY) AS AVG_SALARY

FROM (

SELECT TRUNC((rn - 1) / 3) + 1 AS GROUP_NO

, SALARY

FROM (

SELECT SALARY, ROW_NUMBER() OVER (ORDER BY HIREDATE) AS rn

FROM EMPLOYEE

)

)

GROUP BY GROUP_NO

ORDER BY GROUP_NO

The task is easy to do. We put every n records in one group, such as the first group isrecord1, record2,record3, the second group is record4, record5,record6, and so on. The point is to find the “ordinal number” of the current record and get the grouping condition by dividing the ordinal number by 3 (result rounded up). The problem is that SQL does not have natural “ordinal numbers”. We need to invent them through complicated code and then implement the natural solution using those numbers.

It is easy to code the task using the open-source esProc SPL:

 A 1 =connect("ORACLE").query@x("SELECT * FROM EMPLOYEE ORDER BY HIREDATE") 2 =A1.group@n(((#-1)\3)+1).new(#:GROUP_NO,~.avg(SALARY):AVG_SALARY)

SPL supports using the hash sign # to represent an ordinal number, making it convenient to realize a grouping operation by the result of handling ordinal numbers.