CODEWITHSUNDEEP
sql
warnerst
warnerst

Reputation: 211

How to classify specific data in groups with an sql query

I'm trying to make a query in sql to place specific data in groups. So I want to classify data in the next groups:

[1-90m²] group1
[91-140m²] group2
[141-190m²] group3
[191-300m²] group4
[300+m²] group5

But I only have the exact data.. Example 87m² -->[1-90m²] 160m² --> [141-190m²].

Does anyone know how to do this? Thanks!

Upvotes: 2

Views: 5442

Answers (2)

Andomar
Andomar

Reputation: 238246

Assuming table YourTable has a column woonoppervlakte with data like 87:

select  case
        when woonoppervlakte between 1 and 90 then 'group1'
        when woonoppervlakte between 91 and 140 then 'group2'
        when woonoppervlakte between 141 and 190 then 'group3'
        when woonoppervlakte between 191 and 300 then 'group4'
        when woonoppervlakte > 300 then 'group5'
        end as [Group]
,       *
from    YourTable

Upvotes: 3

mwigdahl
mwigdahl

Reputation: 16588

This is generally known as discretization.

I'm going to assume the values are stored in an integer field without the unit text; if this assumption is wrong or the datatype is not integer you can change this:

DECLARE @test TABLE 
(
    id int IDENTITY,
    value int
)

INSERT INTO @test (value) VALUES
(1),
(2),
(3),
(95),
(140),
(141),
(193),
(240),
(300),
(301)

SELECT value, 
    CASE 
        WHEN value <= 90 THEN 1
        WHEN value <= 140 THEN 2
        WHEN value <= 190 THEN 3
        WHEN value <= 300 THEN 4
        ELSE 5
    END AS group
FROM @test

Upvotes: 5

Related Questions