How to group items into a fixed number of bucket with MS SQL Server
- Microsoft SQL Server has the NTILE() function that allows you to set a specific number of buckets.
- Microsoft SQL Server will allocated the number of row for each bucket.
- See: How to group rows in fixed group size for another way, more accurate way of group items in a fixed number of bucket.
Data used
SELECT productid AS 'Product',
name AS 'Name',
productnumber AS 'Prod Number',
color AS 'Color',
listprice AS 'List Price'
FROM production.product
WHERE color IS not null
and makeflag = 1
and listprice > 2000
ORDER BY productnumber;
name AS 'Name',
productnumber AS 'Prod Number',
color AS 'Color',
listprice AS 'List Price'
FROM production.product
WHERE color IS not null
and makeflag = 1
and listprice > 2000
ORDER BY productnumber;
Product Name Prod Number Color List Price ----------- -------------------------------------------------- ------------------------- --------------- --------------------- 782 Mountain-200 Black, 38 BK-M68B-38 Black 2294.99 783 Mountain-200 Black, 42 BK-M68B-42 Black 2294.99 784 Mountain-200 Black, 46 BK-M68B-46 Black 2294.99 779 Mountain-200 Silver, 38 BK-M68S-38 Silver 2319.99 780 Mountain-200 Silver, 42 BK-M68S-42 Silver 2319.99 781 Mountain-200 Silver, 46 BK-M68S-46 Silver 2319.99 775 Mountain-100 Black, 38 BK-M82B-38 Black 3374.99 776 Mountain-100 Black, 42 BK-M82B-42 Black 3374.99 777 Mountain-100 Black, 44 BK-M82B-44 Black 3374.99 778 Mountain-100 Black, 48 BK-M82B-48 Black 3374.99 771 Mountain-100 Silver, 38 BK-M82S-38 Silver 3399.99 772 Mountain-100 Silver, 42 BK-M82S-42 Silver 3399.99 773 Mountain-100 Silver, 44 BK-M82S-44 Silver 3399.99 774 Mountain-100 Silver, 48 BK-M82S-48 Silver 3399.99 793 Road-250 Black, 44 BK-R89B-44 Black 2443.35 794 Road-250 Black, 48 BK-R89B-48 Black 2443.35 795 Road-250 Black, 52 BK-R89B-52 Black 2443.35 796 Road-250 Black, 58 BK-R89B-58 Black 2443.35 789 Road-250 Red, 44 BK-R89R-44 Red 2443.35 790 Road-250 Red, 48 BK-R89R-48 Red 2443.35 791 Road-250 Red, 52 BK-R89R-52 Red 2443.35 792 Road-250 Red, 58 BK-R89R-58 Red 2443.35 750 Road-150 Red, 44 BK-R93R-44 Red 3578.27 751 Road-150 Red, 48 BK-R93R-48 Red 3578.27 752 Road-150 Red, 52 BK-R93R-52 Red 3578.27 753 Road-150 Red, 56 BK-R93R-56 Red 3578.27 749 Road-150 Red, 62 BK-R93R-62 Red 3578.27 966 Touring-1000 Blue, 46 BK-T79U-46 Blue 2384.07 967 Touring-1000 Blue, 50 BK-T79U-50 Blue 2384.07 968 Touring-1000 Blue, 54 BK-T79U-54 Blue 2384.07 969 Touring-1000 Blue, 60 BK-T79U-60 Blue 2384.07 954 Touring-1000 Yellow, 46 BK-T79Y-46 Yellow 2384.07 955 Touring-1000 Yellow, 50 BK-T79Y-50 Yellow 2384.07 956 Touring-1000 Yellow, 54 BK-T79Y-54 Yellow 2384.07 957 Touring-1000 Yellow, 60 BK-T79Y-60 Yellow 2384.07 (35 row(s) affected)
Grouping items in 5 buckets
SELECT ntile(5) OVER(ORDER BY productnumber) AS 'Grouping',
productid AS 'Product',
name AS 'Name',
productnumber AS 'Prod Number',
color AS 'Color',
listprice AS 'List Price'
FROM production.product
WHERE color IS not null
and makeflag = 1
and listprice > 2000
ORDER BY productnumber;
productid AS 'Product',
name AS 'Name',
productnumber AS 'Prod Number',
color AS 'Color',
listprice AS 'List Price'
FROM production.product
WHERE color IS not null
and makeflag = 1
and listprice > 2000
ORDER BY productnumber;
Grouping Product Name Prod Number Color List Price -------------------- ----------- -------------------------------------------------- ------------------------- --------------- --------------------- 1 782 Mountain-200 Black, 38 BK-M68B-38 Black 2294.99 1 783 Mountain-200 Black, 42 BK-M68B-42 Black 2294.99 1 784 Mountain-200 Black, 46 BK-M68B-46 Black 2294.99 1 779 Mountain-200 Silver, 38 BK-M68S-38 Silver 2319.99 1 780 Mountain-200 Silver, 42 BK-M68S-42 Silver 2319.99 1 781 Mountain-200 Silver, 46 BK-M68S-46 Silver 2319.99 1 775 Mountain-100 Black, 38 BK-M82B-38 Black 3374.99 2 776 Mountain-100 Black, 42 BK-M82B-42 Black 3374.99 2 777 Mountain-100 Black, 44 BK-M82B-44 Black 3374.99 2 778 Mountain-100 Black, 48 BK-M82B-48 Black 3374.99 2 771 Mountain-100 Silver, 38 BK-M82S-38 Silver 3399.99 2 772 Mountain-100 Silver, 42 BK-M82S-42 Silver 3399.99 2 773 Mountain-100 Silver, 44 BK-M82S-44 Silver 3399.99 2 774 Mountain-100 Silver, 48 BK-M82S-48 Silver 3399.99 3 793 Road-250 Black, 44 BK-R89B-44 Black 2443.35 3 794 Road-250 Black, 48 BK-R89B-48 Black 2443.35 3 795 Road-250 Black, 52 BK-R89B-52 Black 2443.35 3 796 Road-250 Black, 58 BK-R89B-58 Black 2443.35 3 789 Road-250 Red, 44 BK-R89R-44 Red 2443.35 3 790 Road-250 Red, 48 BK-R89R-48 Red 2443.35 3 791 Road-250 Red, 52 BK-R89R-52 Red 2443.35 4 792 Road-250 Red, 58 BK-R89R-58 Red 2443.35 4 750 Road-150 Red, 44 BK-R93R-44 Red 3578.27 4 751 Road-150 Red, 48 BK-R93R-48 Red 3578.27 4 752 Road-150 Red, 52 BK-R93R-52 Red 3578.27 4 753 Road-150 Red, 56 BK-R93R-56 Red 3578.27 4 749 Road-150 Red, 62 BK-R93R-62 Red 3578.27 4 966 Touring-1000 Blue, 46 BK-T79U-46 Blue 2384.07 5 967 Touring-1000 Blue, 50 BK-T79U-50 Blue 2384.07 5 968 Touring-1000 Blue, 54 BK-T79U-54 Blue 2384.07 5 969 Touring-1000 Blue, 60 BK-T79U-60 Blue 2384.07 5 954 Touring-1000 Yellow, 46 BK-T79Y-46 Yellow 2384.07 5 955 Touring-1000 Yellow, 50 BK-T79Y-50 Yellow 2384.07 5 956 Touring-1000 Yellow, 54 BK-T79Y-54 Yellow 2384.07 5 957 Touring-1000 Yellow, 60 BK-T79Y-60 Yellow 2384.07 (35 row(s) affected)
Grouping items in 10 buckets
SELECT ntile(10) OVER(ORDER BY productnumber) AS 'Grouping',
productid AS 'Product',
name AS 'Name',
productnumber AS 'Prod Number',
color AS 'Color',
listprice AS 'List Price'
FROM production.product
WHERE color IS not null
and makeflag = 1
and listprice > 2000
ORDER BY productnumber;
productid AS 'Product',
name AS 'Name',
productnumber AS 'Prod Number',
color AS 'Color',
listprice AS 'List Price'
FROM production.product
WHERE color IS not null
and makeflag = 1
and listprice > 2000
ORDER BY productnumber;
Grouping Product Name Prod Number Color List Price -------------------- ----------- -------------------------------------------------- ------------------------- --------------- --------------------- 1 782 Mountain-200 Black, 38 BK-M68B-38 Black 2294.99 1 783 Mountain-200 Black, 42 BK-M68B-42 Black 2294.99 1 784 Mountain-200 Black, 46 BK-M68B-46 Black 2294.99 1 779 Mountain-200 Silver, 38 BK-M68S-38 Silver 2319.99 2 780 Mountain-200 Silver, 42 BK-M68S-42 Silver 2319.99 2 781 Mountain-200 Silver, 46 BK-M68S-46 Silver 2319.99 2 775 Mountain-100 Black, 38 BK-M82B-38 Black 3374.99 2 776 Mountain-100 Black, 42 BK-M82B-42 Black 3374.99 3 777 Mountain-100 Black, 44 BK-M82B-44 Black 3374.99 3 778 Mountain-100 Black, 48 BK-M82B-48 Black 3374.99 3 771 Mountain-100 Silver, 38 BK-M82S-38 Silver 3399.99 3 772 Mountain-100 Silver, 42 BK-M82S-42 Silver 3399.99 4 773 Mountain-100 Silver, 44 BK-M82S-44 Silver 3399.99 4 774 Mountain-100 Silver, 48 BK-M82S-48 Silver 3399.99 4 793 Road-250 Black, 44 BK-R89B-44 Black 2443.35 4 794 Road-250 Black, 48 BK-R89B-48 Black 2443.35 5 795 Road-250 Black, 52 BK-R89B-52 Black 2443.35 5 796 Road-250 Black, 58 BK-R89B-58 Black 2443.35 5 789 Road-250 Red, 44 BK-R89R-44 Red 2443.35 5 790 Road-250 Red, 48 BK-R89R-48 Red 2443.35 6 791 Road-250 Red, 52 BK-R89R-52 Red 2443.35 6 792 Road-250 Red, 58 BK-R89R-58 Red 2443.35 6 750 Road-150 Red, 44 BK-R93R-44 Red 3578.27 7 751 Road-150 Red, 48 BK-R93R-48 Red 3578.27 7 752 Road-150 Red, 52 BK-R93R-52 Red 3578.27 7 753 Road-150 Red, 56 BK-R93R-56 Red 3578.27 8 749 Road-150 Red, 62 BK-R93R-62 Red 3578.27 8 966 Touring-1000 Blue, 46 BK-T79U-46 Blue 2384.07 8 967 Touring-1000 Blue, 50 BK-T79U-50 Blue 2384.07 9 968 Touring-1000 Blue, 54 BK-T79U-54 Blue 2384.07 9 969 Touring-1000 Blue, 60 BK-T79U-60 Blue 2384.07 9 954 Touring-1000 Yellow, 46 BK-T79Y-46 Yellow 2384.07 10 955 Touring-1000 Yellow, 50 BK-T79Y-50 Yellow 2384.07 10 956 Touring-1000 Yellow, 54 BK-T79Y-54 Yellow 2384.07 10 957 Touring-1000 Yellow, 60 BK-T79Y-60 Yellow 2384.07 (35 row(s) affected)
The way Microsoft SQL Server groups rows in buckets in easier to see in the second example of 10 buckets.
- If the number of rows is directly divisible by the number of buckets or rows modulus buckets = 0, the number of rows in each bucket will be identical.
- If the number of rows is NOT directly divisible by the number of buckets or rows modulus buckets <> 0, SQL Server will spread the difference at the end of the table. Bucket 1 to 5 have 4 rows, and bucket 6 to 10 have only 3 rows.