CODEWITHSUNDEEP
sql
coson
coson

Reputation: 8659

SQL Grouping by data

Good Day,

I have a sql query that returns the following data:

EmployeeID...Employee
555..........John Doe
666..........Jane Doe
777..........Bob Smith
888..........Jane Smith
999..........Fred Jones
000..........Freda Jones

and I want my query to group by items of three:

Item.........EmployeeId........Employee
1............555...............John Doe
1............666...............Jane Doe
1............777...............Bob Smith
2............888...............Jane Smith
2............999...............Fred Jones
2............000...............Freda Jones

In other words, for every record that has more than 3 records, I want to increment Item by one every three records..

I am sure that I can use some sort of row insert into a table and keep track of the number of rows inserted into a table. But I'm trying to see if I can pull this off in SQL itself.

Is this possible?

TIA,

coson

Upvotes: 0

Views: 91

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Well, this should work in most DBMS:

select floor((count(*) + 2) / 3) item, t1.employeeId, t1.employee
from t t1
join t t2 on t1.employeeId >= t2.employeeId
group by t1.employeeId, t1.employee 
order by t1.employeeId

You can play with the fiddle here.

Upvotes: 1

Andriy M
Andriy M

Reputation: 77657

If your database's SQL variety supports ranking functions, you could try something like this:

SELECT
  FLOOR((ROW_NUMBER() OVER (ORDER BY EmployeeID) + 2) / 3) AS Item,
  EmployeeID,
  Employee
FROM Employees
ORDER BY EmployeeID

Some database systems automatically perform integral division when both operands are integers, others support a dedicated operator (like DIV) for integral division instead of /. In both mentioned cases FLOOR() would be unnecessary, of course.

Upvotes: 1

Related Questions