**Case Function
** A flexible method of grouping data into even or unevenly sized buckets.

Very similar to DECODE

Syntax:

CASE WHEN <cond1> THEN <Value1> WHEN <cond2> THEN <Value2> [ELSE Value n ] END

A single CASE statement can be selected (along with other columns), resulting in a vertical list of data buckets. Alternatively several case statements can be summed to display totals in a horizontal row:

SELECT CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END FROM emp; SELECT SUM(CASE WHEN SUM(amount) BETWEEN 0 AND 49 THEN 1 ELSE 0 END) AS "0-49", SUM(CASE WHEN SUM(amount) BETWEEN 50 AND 100 THEN 1 ELSE 0 END) AS "50-100" FROM sales;

** WIDTH_BUCKET Function**

Divide a data set into buckets with an equal interval size.

e.g. Age = 0-20, 20-40, 40-60, 60-80…

This is known as an 'equiwidth histogram'.

Syntax:

WIDTH_BUCKET(column/expr, low boundary, high_boundary, bucket_count)

If you ask for (n) buckets you actually get (n+2) buckets

The extra 2 being for values above and below the high/low boundaries.

e.g.

SELECT last_name, salary,

WIDTH_BUCKET(salary,3000,9000,3)

Will create 5 buckets:

Up_to_3000, 3000-5000, 5000-7000, 7000-9000, 9000+

When using WIDTH_BUCKET pay attention to the boundary values, each bucket will contain values equal to or greater than the lowest
boundary of that bucket, so age ranges 0-20, 20-40… would actually be 0-19.99... and 20-39.999…

*"Our team is well balanced. We have problems everywhere" ~ Tommy Prothro*

**Related**

Analytic features

Oracle SQL Functions

Copyright © 1999-2021 SS64.com

Some rights reserved

Some rights reserved