Recipe12.9.Creating Horizontal Histograms

Recipe 12.9. Creating Horizontal Histograms

Problem

You want to use SQL to generate histograms that extend horizontally. For example, you want to display the number of employees in each department as a horizontal histogram with each employee represented by an instance of "*". You want to return the following result set:

 DEPTNO CNT ------ ----------     10 ***     20 *****     30 ****** 

Solution

The key to this solution is to use the aggregate function COUNT, and use GROUP BY DEPTNO to determine the number of employees in each DEPTNO. The value returned by COUNT is then passed to a string function that generates a series of "*" characters.

DB2

Use the REPEAT function to generate the histogram:

 1 select deptno, 2        repeat('*',count(*)) cnt 3   from emp 4  group by deptno 

Oracle, PostgreSQL, and MySQL

Use the LPAD function to generate the needed strings of "*" characters:

 1 select deptno, 2        lpad('*',count(*),'*') as cnt 3   from emp 4  group by deptno 

SQL Server

Generate the histogram using the REPLICATE function:

 1 select deptno, 2        replicate('*',count(*)) cnt 3   from emp 4  group by deptno 

Discussion

The technique is the same for all vendors. The only difference lies in the string function used to return a "*" for each employee. The Oracle solution will be used for this discussion, but the explanation is relevant for all the solutions.

The first step is to count the number of employees in each department:

  select deptno,        count(*)   from emp  group by deptno DEPTNO   COUNT(*) ------ ----------     10          3     20          5     30          6 

The next step is to use the value returned by COUNT(*) to control the number of "*"characters to return for each department. Simply pass COUNT(*) as an argument to the string function LPAD to return the desired number of "*"s:

  select deptno,        lpad('*',count(*),'*') as cnt   from emp  group by deptno DEPTNO CNT ------ ----------     10 ***     20 *****     30 ****** 

For PostgreSQL users, you may need to explicitly cast the value returned by COUNT(*) to an integer as can be seen below:

  select deptno,        lpad('*',count(*)::integer,'*') as cnt   from emp  group by deptno DEPTNO CNT ------ ----------     10 ***     20 *****     30 ****** 

This CAST is necessary because PostgreSQL requires the numeric argument to LPAD to be an integer.