clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1579714 fiddles created (21360 in the last week).

create table t (id int, title varchar(100), price decimal(18,2)); insert into t (id, title, price) values (19, 'Deux fillettes, fond jaune et rouge ', 11), (17, 'Flowers in a Pitcher ', 12), (5 , 'Composition with Red, Yellow and Blue ', 12), (18, 'La lecon de musique (The Music Lesson) ', 12), (9 , 'The Adoration of the Magi ', 12), (29, 'Self-Portrait ', 14), (25, 'Symphony in White, No. 1: The White Girl ', 14), (30, 'The Anatomy Lecture of Dr. Nicolaes Tulp ', 14), (20, 'Les repasseuses (Women Ironing) ', 14), (1 , 'The Birth of Venus ', 15), (12, 'Femme se promenant dans une foret exotique ', 15), (24, 'Portrait of the Painter’s Mother ', 15), (28, 'Jeunes filles au piano ', 15), (7 , 'Portrait de l artiste (Self-portrait) ', 16), (3 , 'The Last Supper ', 16), (13, 'Combat of a Tiger and a Buffalo ', 16), (4 , 'The Creation of Man ', 17), (22, 'Le Chemin de Fer ', 17), (6 , 'Femmes de Tahiti [Sur la plage] ', 18), (21, 'Le Bar aux Folies-Berg ', 18), (26, 'Lady at the Piano ', 18), (15, 'Remembrance of a Garden ', 18), (16, '1914 ', 18), (14, 'Ancient Sound, Abstract on Black ', 19), (8 , 'The Large Turf ', 19), (23, 'On the Beach ', 19), (2 , 'Portrait of Mona Lisa ', 19), (27, 'On the Terrace ', 20), (10, 'The She-Wolf ', 20);
29 rows affected
 hidden batch(es)


CREATE FUNCTION dbo.with_bucket(@val decimal, @min_val decimal, @max_val decimal, @groups int) RETURNS int AS BEGIN DECLARE @res int; IF @val = @max_val RETURN @groups; RETURN CAST((@val - @min_val) / ((@max_val - @min_val) / @groups) AS int) + 1; END
 hidden batch(es)


SELECT id, title, price, dbo.with_bucket(price, 11.0, 20.0, 10) FROM t;
id title price (No column name)
19 Deux fillettes, fond jaune et rouge 11.00 1
17 Flowers in a Pitcher 12.00 2
5 Composition with Red, Yellow and Blue 12.00 2
18 La lecon de musique (The Music Lesson) 12.00 2
9 The Adoration of the Magi 12.00 2
29 Self-Portrait 14.00 4
25 Symphony in White, No. 1: The White Girl 14.00 4
30 The Anatomy Lecture of Dr. Nicolaes Tulp 14.00 4
20 Les repasseuses (Women Ironing) 14.00 4
1 The Birth of Venus 15.00 5
12 Femme se promenant dans une foret exotique 15.00 5
24 Portrait of the Painter’s Mother 15.00 5
28 Jeunes filles au piano 15.00 5
7 Portrait de l artiste (Self-portrait) 16.00 6
3 The Last Supper 16.00 6
13 Combat of a Tiger and a Buffalo 16.00 6
4 The Creation of Man 17.00 7
22 Le Chemin de Fer 17.00 7
6 Femmes de Tahiti [Sur la plage] 18.00 8
21 Le Bar aux Folies-Berg 18.00 8
26 Lady at the Piano 18.00 8
15 Remembrance of a Garden 18.00 8
16 1914 18.00 8
14 Ancient Sound, Abstract on Black 19.00 9
8 The Large Turf 19.00 9
23 On the Beach 19.00 9
2 Portrait of Mona Lisa 19.00 9
27 On the Terrace 20.00 10
10 The She-Wolf 20.00 10
 hidden batch(es)


DECLARE @groups int = 10; WITH mm AS ( SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM t ) SELECT id, title, price, CASE WHEN price = max_price THEN @groups ELSE CAST((price - min_price) / ((max_price - min_price) / @groups) AS int) + 1 END as ngroup FROM t CROSS APPLY (SELECT min_price, max_price FROM mm) mm;
id title price ngroup
19 Deux fillettes, fond jaune et rouge 11.00 1
17 Flowers in a Pitcher 12.00 2
5 Composition with Red, Yellow and Blue 12.00 2
18 La lecon de musique (The Music Lesson) 12.00 2
9 The Adoration of the Magi 12.00 2
29 Self-Portrait 14.00 4
25 Symphony in White, No. 1: The White Girl 14.00 4
30 The Anatomy Lecture of Dr. Nicolaes Tulp 14.00 4
20 Les repasseuses (Women Ironing) 14.00 4
1 The Birth of Venus 15.00 5
12 Femme se promenant dans une foret exotique 15.00 5
24 Portrait of the Painter’s Mother 15.00 5
28 Jeunes filles au piano 15.00 5
7 Portrait de l artiste (Self-portrait) 16.00 6
3 The Last Supper 16.00 6
13 Combat of a Tiger and a Buffalo 16.00 6
4 The Creation of Man 17.00 7
22 Le Chemin de Fer 17.00 7
6 Femmes de Tahiti [Sur la plage] 18.00 8
21 Le Bar aux Folies-Berg 18.00 8
26 Lady at the Piano 18.00 8
15 Remembrance of a Garden 18.00 8
16 1914 18.00 8
14 Ancient Sound, Abstract on Black 19.00 9
8 The Large Turf 19.00 9
23 On the Beach 19.00 9
2 Portrait of Mona Lisa 19.00 9
27 On the Terrace 20.00 10
10 The She-Wolf 20.00 10
 hidden batch(es)