# How SQL Groups Data by Position

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 divide rows evenly into three groups according to hire date and calculate the average salary in each group.

That is, grouping employee records by HIREDATE (putting the first one-third of the records in the first group, the next one-third to the second group, and the last one-third to the third group), 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 / (

SELECT count(*)

FROM EMPLOYEE

)) + 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

Actually, it is simple to do the task – getting the position of each record in the table in turn and grouping records by their positions. The key is to get the positions. And to find the position for a record, we need to know its ordinal number. The problem is that SQL does not have natural “ordinal numbers”. We need to invent them through complicated code and then get positions according to those numbers.

But it is easy to achieve 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\A1.len()+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.