Display MySQL Histogram with Negative Values

For negative values, use reverse() along with concat(). Let us first create a table −

mysql> create table DemoTable632 (
   histogramId int NOT NULL AUTO_INCREMENT PRIMARY KEY,histogramValue int,histogramImage text
);
Query OK, 0 rows affected (0.78 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable632(histogramValue) values(2);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable632(histogramValue) values(3);
Query OK, 1 row affected (0.34 sec)
mysql> insert into DemoTable632(histogramValue) values(-6);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable632(histogramValue) values(-5);
Query OK, 1 row affected (0.13 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable632;

This will produce the following output −

+-------------+----------------+----------------+
| histogramId | histogramValue | histogramImage |
+-------------+----------------+----------------+
|           1 |              2 | NULL          |
|           2 |              3 | NULL          |
|           3 |             -6 | NULL          |
|           4 |             -5 | NULL          |
+-------------+----------------+----------------+
4 rows in set (0.00 sec)

Here is the query to histogram with negative values −

mysql> SELECT histogramId, histogramValue, CONCAT(
      REVERSE(RPAD(REPEAT('*', IF(histogramValue<0,-histogramValue,0)), 100, ' ')),
      REPEAT('*',IF(histogramValue<0,0,histogramValue))
   )
AS histogramImage FROM DemoTable632 ORDER BY histogramId;

This will produce the following output −

+-------------+----------------+----------------+
| histogramId | histogramValue | histogramImage |
+-------------+----------------+----------------+
|           1 | 2              | **             |
|           2 | 3              | ***            |
|           3 | -6             | ******         |
|           4 | -5             | *****          |
+-------------+----------------+----------------+
4 rows in set (0.00 sec)