CODEWITHSUNDEEP
sqloracle-database
Ihraz
Ihraz

Reputation: 48

break into ranges using sql

I am running following sql.

SELECT value FROM ppr_price_factor WHERE product_factor_id=3;

which returns values like this

17

25

35

45

I need to display my results as something like below.

value     value Range
17        0-17

25        18-25

35        26-35

45        36-45

Can I do this using sql?

Upvotes: 0

Views: 53

Answers (3)

G.Nader
G.Nader

Reputation: 857

Use this :

SELECT value,
       TO_CHAR(1 + LAG(value, 1, -1) OVER (ORDER by value)) || '-' || TO_CHAR(value) AS value_range
  FROM your_table

Upvotes: 0

DirkNM
DirkNM

Reputation: 2664

Maybe that helps:

SELECT value,
       TO_CHAR(1 + LAG(value, 1, -1) OVER (ORDER by value)) || '-' || TO_CHAR(value) AS value_range
  FROM your_table

Upvotes: 1

Logan
Logan

Reputation: 1371

I would use case statement for the respective problem.

You can use the following sql as a reference

SELECT value
    ,CASE 
        WHEN value >= 0
            AND value <= 17
            THEN '0-17'
        WHEN value >= 18
            AND value <= 25
            THEN '18-25'
        WHEN value >= 26
            AND value <= 35
            THEN '26-35'
        WHEN value >= 36
            AND value <= 45
            THEN '36-45'
        ELSE '46-'
        END AS valueRange
FROM table1

sql fiddle link

Upvotes: 0

Related Questions