# How-to: CASE and Width_Bucket Functions

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