BAR_CHART Operator

You can use the SQL operator BAR_CHART to display a numeric or Boolean value as a bar of characters. You can specify the range represented by the bar, its width, and the character used to draw it.

The operator returns a character representation for display with the data in a query result.

Syntax

Description of the illustration bar_chart.png

The BAR_CHART operator requires the value operand. You can optionally specify maximum, minimum, width, and symbol, in that order. If you specify an optional operand, you must also specify all preceding optional operands.

Semantics

value

The first operand value is a numeric or Boolean expression that determines the length of the bar. It can be a numeric literal, column value, bind variable, or another numeric or Boolean expression. For a numeric value, BAR_CHART maps the value to a bar between length 0 and width, based on the range from minimum to maximum.

If value is less than minimum, it is treated as minimum. If value is greater than maximum, it is treated as maximum. If value is null, the operator returns null.

For a Boolean value, TRUE is treated as 1 and FALSE as 0.

maximum

The second operand maximum specifies the upper bound of the scale. It can be a numeric literal, column value, bind variable, or another numeric expression. The default is 100 for numeric values and 1 for Boolean values. If maximum is null, or if it is less than minimum, the operator returns null.

minimum

The third operand minimum specifies the lower bound of the scale. It can be a numeric literal, column value, bind variable, or another numeric expression. The default is 0. If minimum is null, or if it is greater than maximum, the operator returns null.

The default range from 0 to 100 is useful for displaying percentage values.

width

The fourth operand width specifies the number of characters in the bar. It can be a numeric literal, column value, bind variable, or another numeric expression. Specify a non-null value from 1 through 1000. The default is 10 for numeric values and 1 for Boolean values.

If the bar does not fill the specified width, the result is padded with spaces. A small non-null value can therefore produce a result containing only spaces, which distinguishes it from a null value.

symbol

The fifth operand symbol specifies the character used to draw the bar. It can be a character literal, column value, bind variable, or another character expression. The value must contain exactly one character. A null or empty value is not allowed.

In a non-UTF-8 database, symbol must be a 7-bit ASCII character. The default is '#'.

In a UTF-8 database, symbol can be a Unicode character. The default is '|'. When you specify '|', the operator can display full and partial block characters. This provides up to nine fractional block widths and allows more precise results when width is small. Other symbols are displayed as whole characters.

You can use BAR_CHART in the select list, ORDER BY clause, or GROUP BY clause. You can also concatenate chart results. When you group by BAR_CHART, different input values that produce the same bar are placed in the same group.

Examples

Using the Default Range and Width

The default scale is 0 to 100 and the default bar width is 10 characters. For example:

SELECT sal, BAR_CHART(sal/50)
FROM emp;

This example divides each SAL value by 50. A salary of 800 produces a short bar, while a salary of 5000 fills the bar. The characters used for the bar depend on the database character set.

Using Different Character Sets and Symbols

For example, in a non-UTF-8 database, the following statement uses '#' as the bar character:

SELECT sal, BAR_CHART(sal/50, 100, 0, 10, '#')
FROM emp;

This example specifies the default numeric range and width explicitly. In a UTF-8 database, you can use a Unicode character and compare the output produced by different symbols:

SELECT sal, BAR_CHART(sal/50, 100, 0, 2, '|'), BAR_CHART(sal/50, 100, 0, 2, '#')
FROM emp;

This example compares '|' with '#' at a width of two characters. The '|' version can show a partial block, whereas the '#' version uses whole characters.

Using Boolean Expressions

For example, the following statement displays one-character bars for three Boolean conditions:

SELECT BAR_CHART(sal > 2000) "A",
       BAR_CHART(comm IS NOT NULL) "B",
       BAR_CHART(job = 'MANAGER') "C"
FROM emp;

This example returns a bar for each TRUE result and a space for each FALSE result.

You can also specify the range, width, and symbol for a Boolean expression. For example:

SELECT employee_id,
       BAR_CHART(salary >= 10000, 1, 0, 1, '#') AS high_salary
FROM employees;

This example displays '#' when the salary is at least 10000 and a space otherwise.

Choosing a Symbol for Each Row

For example, in a UTF-8 database, you can use a CASE expression to choose a symbol according to the employee’s job:

SELECT ename,
       BAR_CHART(sal/50, 100, 0, 20,
         CASE
           WHEN job = 'CLERK' THEN '📁'
           WHEN job = 'MANAGER' THEN '😡'
           WHEN job = 'SALESMAN' THEN '💰'
           WHEN job = 'ANALYST' THEN '📓'
           WHEN job = 'PRESIDENT' THEN '🍺'
           ELSE '*'
         END)
FROM emp;

This example displays the symbol selected for each row. These symbols are displayed as whole characters, not partial blocks.

Concatenating Bars

For example, in a UTF-8 database, you can combine multiple bars to represent separate portions of a scale:

SELECT ename,
       BAR_CHART(sal/50, 33, 0, 10, '🟢') ||
       BAR_CHART(sal/50, 66, 34, 10, '🟡') ||
       BAR_CHART(sal/50, 100, 67, 10, '🔴')
FROM emp;

This example concatenates three bars. Each bar uses a different range and symbol to represent a portion of the 0-to-100 scale.

Specifying a Range and Width

For example, the following statement displays each employee’s salary with a 20-character bar:

SELECT employee_id, salary,
       BAR_CHART(salary, 25000, 0, 20, '#') AS salary_chart
FROM employees
ORDER BY salary;

This example uses a range of 0 through 25000 and uses '#' to draw the bar.

Using the ORDER BY Clause

You can use BAR_CHART in an ORDER BY clause. For example:

SELECT ename,
       BAR_CHART(sal/50, 100, 0, 20, '|')
FROM emp
ORDER BY 2;

This example uses ORDER BY 2 to refer to the chart expression in the second select-list position. Oracle orders the rows by the input value instead of comparing the generated chart strings. Therefore, this statement is effectively ordered by sal.

Using the GROUP BY Clause

You can use BAR_CHART in a GROUP BY clause. For example:

SELECT COUNT(1),
       BAR_CHART(sal/50, 100, 0, 4, '|')
FROM emp
GROUP BY BAR_CHART(sal/50, 100, 0, 4, '|');

This example groups rows that produce the same four-character bar. Different input values can produce the same bar, so those rows are placed in the same group.

See Also:

• Concatenation Operator

• About SQL Operators