CODEWITHSUNDEEP
mysqlbar-chart
dialogik
dialogik

Reputation: 9552

Create bar chart only using MySQL query

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?

Upvotes: 1

Views: 400

Answers (1)

dialogik
dialogik

Reputation: 9552

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 | ########

Upvotes: 1

Related Questions