How to Create a Bar Chart with SQL Code

By:   |   Updated: 2022-09-16   |   Comments   |   Related: More > TSQL


Problem

Recently, I was asked to do a real off-the-wall task. My client wanted to see a bar chart of the "averaged" values in a SQL table. My first thought was, "why?" But then I thought, what a challenge. As a DBA, I encourage you to accept such challenges enthusiastically. They provide a great experience and practice for you to grow your skill set.

Solution

In this SQL tutorial, we will walk through the steps to create a bar chart, just as my client asked. We will create a test table and some data and with that, we will produce an actual bar chart that can be viewed in a SQL query or even be used in a view based on the SQL SELECT query we will build in this tutorial.

Create Test Table

We want to create a bar chart that represents the average grade of each of our four students. The following code block will create the table we will work with.

CREATE TABLE StudentScore (
     student VARCHAR(20)
   , class VARCHAR(20)
   , score INT
   );
GO

Add Sample Data

INSERT INTO StudentScore(student, class, score)
VALUES('John', 'math', 98)
    , ('John', 'history', 76)
    , ('John', 'science', 80)
    , ('John', 'chemistry', 78)
    , ('Suzie', 'math', 70)
    , ('Suzie', 'history', 78)
    , ('Suzie', 'science', 70)
    , ('Suzie', 'chemistry', 70)
    , ('Jim', 'math', 50)
    , ('Jim', 'history', 50)
    , ('Jim', 'science', 50)
    , ('Jim', 'chemistry', 50)
    , ('Cheryl', 'math', 90)
    , ('Cheryl', 'history', 82)
    , ('Cheryl', 'science', 80)
    , ('Cheryl', 'chemistry', 70)
GO

Querying the SQL Table

Now that we have our test table and data set, let's run a simple query to return each student's minimum, maximum, and average grades. We need to use the GROUP BY clause since we are using the min, max, and avg aggregate functions.

SELECT student
   , MIN(Score) AS 'MinScore'
   , AVG(Score) AS 'Average'
   , MAX(Score) AS 'MaxScore'
FROM StudentScore
GROUP BY student;

Results:

Results of simple query

Now that we know the average scores, let's work on producing a column that shows a simple bar that reflects that average score for each student.

Building the Bar Chart

Let's start with learning how to create a simple bar using the SQL REPLICATE() function.

SELECT REPLICATE('_', 50);

Results:

Simple bar using the SQL REPLICATE() function

Notice that we returned 50 underscores to form a single line. Of course, you can use whatever character you like. For example, you could use the asterisk (*) to simulate an old Dot Matrix printout.

Simple Bar Chart Example

To simply show the bar for the data, we can use the following query. Since we know the top test score would be 100, we know the most the bar would be is 100 underscores.

SELECT student,
       AVG(Score) AS AvgScore,
       REPLICATE('_', AVG(Score)) AS Graph
FROM StudentScore
GROUP BY student;
GO
sample bar chart

Scaling the Bar Chart

For other data where the top value might be in the thousands or millions, we would need to scale the size of the bar otherwise it would be too large to display. To do this for our example, we could find the MAX score (which is 98 for our sample data) and use this as the top level value and divide the average score with this value. To then scale up or down the output we could use a multiplier between 1 and 100 (1% and 100%).

Here's the final query that pulls it all together. In this example the bar will be 40% of the size (using a value of 40.0) if we are using a scale from 1 to 100.

SELECT student,
       AVG(Score) AS AvgScore,
       REPLICATE('_', ROUND(CAST(AVG(Score) AS float) * 40.0 / 
          CAST((SELECT MAX(SCORE) FROM Studentscore) AS float),0)) AS Graph
FROM StudentScore
GROUP BY student;
GO

Results:

Applying the Scaled Bar to the Table

Lines 3 and 4 in the code block above are where the "graph" is constructed. Reading through the two lines of code lets you see what is going on. Here's a simple breakdown.

In line 3, we grab the average score for each student (one at a time), rounding it as a float and multiplying by 40. Line 4 grabs the maximum score of all student scores as a float. Next, we use the REPLICATE() function to display a series of underscores. The quantity of underscores is determined by the average and maximum numbers as rounded floats based on our multiplier.

Wrap Up

In this tutorial, we used the following SQL functions along with GROUP BY to produce a simple bar chart graph on the values of a column.

  1. AVG()
  2. MIN()
  3. MAX()
  4. REPLICATE()
  5. ROUND()
  6. CAST() 
Next Steps



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Aubrey Love Aubrey Love has been a Database Administrator for about 8 years and is currently working as a Microsoft SQL Server Business Intelligence specialist.

View all my tips


Article Last Updated: 2022-09-16

Comments For This Article

















get free sql tips
agree to terms