Create bar chart only using MySQL query

Question

I once saw a MySQL result where one fetched amount was displayed with * as bar charts. Something like this.

id | amount | bar
----------------------------
 1 |      7 | *******
 2 |      4 | ****
 3 |      0 | 
 4 |      3 | ***
 5 |      9 | *********
 6 |     10 | **********
 7 |      6 | ******
 8 |      3 | ***
 9 |      2 | **
 10|      0 |

If I remember correctly, it was some kind of RANGE() function that was used in the query, like

SELECT
    `id`,
    COUNT(*) AS `amount`,
    RANGE(`amount`) AS `bar`
FROM
    `table`

But I was neither successful researching on google or SO. Anyone knows how to do this or can forward me to the appropriate SO post?

@SujithKarivelil I don't want to use PHP. I want to use a MySQL query exclusively. — dialogik
– dialogik, Commented Jul 19, 2014 at 11:50

Community · Accepted Answer · 2020-06-20 09:12:55Z

Strawberry had the clue in his comment. The function's name I was looking for is REPEAT(). I funnily couldn't find it in the MySQL documentation, so I'm referring to w3resource (if anyone can find official docu, feel free to edit).

Syntax
REPEAT(str, count)
str A string which is to be repeated.

count An integer indicating how many times the string str is to be repeated.

So the function can be used like this

SELECT
    `id`,
    COUNT(*) AS `amount`,
    REPEAT('#' , COUNT(`amount`)) AS `bar`
FROM
    `table`

Which returns an ouput like

id | amount | bar
----------------------------
 1 |      4 | ####
 2 |     14 | ##############
 3 |      8 | ########
 4 |      5 | #####
 5 |      3 | ###
 6 |     11 | ###########

The count parameter can also be used to stretch or shrink the bar, e.g. by multiplication - or division, like this

...
  REPEAT('#', COUNT(`amount`)/4)) AS `bar`
...

would return things like this

id | amount | bar
----------------------
 1 |     20 | #####
 2 |     12 | ###
 3 |     32 | ########

Collectives™ on Stack Overflow

Create bar chart only using MySQL query

1 Answer 1

Syntax

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Syntax

Comments

Your Answer

Sign up or log in

Post as a guest

Related