This is one of the most impressive demonstrations of SQL I’ve seen. Shlomi Noach, a database consultant from Israel, has developed a single SQL query that analyzes a table and generates a pie chart in ASCII art.
Shlomi’s blog post, SQL pie chart, provides a full step-by-step analysis of how it was achieved. In summary:
- Values from a single table are examined and accumulating totals are calculated.
- From the accumulating totals, accumulating ratios are calculated in a 0 to 1 range and 0 to 2 * PI range (radians in a whole circle).
- A co-ordinates table is then created. This starts at zero in the top-left and increases in appropriate values to a maximum size in the bottom right.
- The co-ordinates table is used to generate a table of degrees in relation to the center of the table.
- The original table data is then mapped on to the degrees table, assigned an ASCII character for each ‘color’, and anything outside the circle is set to a space.
- Finally, a legend is added.
+----------------------------------------------------------------------------------------------+ | pie chart | +----------------------------------------------------------------------------------------------+ | | | ;;;;;;;;;;;;;;;;;;;;;;;;; | | oooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; | | ooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; | | oooooooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; | | oooooooooooooooooooooooo ;;;;;;;;;;;;;;;;;;;;;### | | oooooooooooooooooooo ;;;;;;;;;########### | | oooooooooooooooooo ################## | | ooooooooooooooooo ################# | | ooooooooooooooooo ################# | | oooooooooooooooo ################ | | oooooooooooooooo ################ | | oooooooooooooooo :::::::::::::::: | | ooooooooooooooooo ::::::::::::::::: | | ooooooooooooooooo ::::::::::::::::: | | oooooooooooooo:::: :::::::::::::::::: | | ooooooo::::::::::::: :::::::::::::::::::: | | :::::::::::::::::::::::: :::::::::::::::::::::::: | | ::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::: | | ::::::::::::::::::::::::::::::::::::::::::::::::::::::: | | ::::::::::::::::::::::::::::::::::::::::::: | | ::::::::::::::::::::::::: | | | | ## red: 1 (10%) | | ;; blue: 2 (20%) | | oo orange: 3 (30%) | | :: white: 4 (40%) | +----------------------------------------------------------------------------------------------+As you might imagine, the resulting query is a little long and complex. I won’t reproduce it here but Shlomi’s post provides the code if you want to try it for yourself. It’s very clever. And utterly mad. Have you seen any other examples of bizarre SQL queries? Post your links below…
Frequently Asked Questions about SQL Pie Charts
How can I create a pie chart using SQL?
Creating a pie chart using SQL involves several steps. First, you need to gather your data from your database using a SELECT statement. This data should be grouped in a way that makes sense for your pie chart. For example, if you’re creating a pie chart of sales by region, you might group your data by region. Once you have your data, you can use a tool like Excel or Google Sheets to create the pie chart. Simply import your data into the tool, select the appropriate columns for your pie chart, and choose the pie chart option.
Can I use SQL to create other types of charts?
Yes, you can use SQL to gather data for any type of chart. The process is similar to creating a pie chart. You would use a SELECT statement to gather your data, group it appropriately, and then import it into a tool that can create the type of chart you want. This could be a bar chart, line chart, scatter plot, or any other type of chart.
What tools can I use to create a pie chart from SQL data?
There are many tools you can use to create a pie chart from SQL data. Some of the most popular include Excel, Google Sheets, and Tableau. These tools allow you to import your data and then choose the type of chart you want to create.
How can I group my data for a pie chart in SQL?
Grouping data for a pie chart in SQL is done using the GROUP BY clause in your SELECT statement. This allows you to group your data by a specific column. For example, if you’re creating a pie chart of sales by region, you might group your data by the region column.
Can I create a pie chart directly in SQL?
SQL is a language for managing and manipulating data, not for creating visualizations. So while you can use SQL to gather and prepare your data, you’ll need to use another tool to actually create the pie chart.
How can I import my SQL data into Excel or Google Sheets?
Both Excel and Google Sheets allow you to import data directly from a database. In Excel, you can use the “Get Data” option in the “Data” tab. In Google Sheets, you can use the “Data Connector for BigQuery” add-on.
What is the best way to visualize data from SQL?
The best way to visualize data from SQL depends on the nature of your data and what you want to convey. Pie charts are great for showing proportions, while bar charts are good for comparing quantities. Line charts are useful for showing trends over time.
Can I use SQL to create a pie chart with multiple categories?
Yes, you can use SQL to gather data for a pie chart with multiple categories. You would need to group your data by more than one column in your SELECT statement.
How can I customize my pie chart created from SQL data?
Customizing your pie chart is done in the tool you use to create the chart, not in SQL. Most tools allow you to change the colors, labels, title, and other aspects of the chart.
Can I use SQL to update my pie chart in real time?
While SQL itself can’t update a pie chart in real time, you can set up your chart in a tool like Tableau to automatically update as new data is added to your database. This involves setting up a live connection between your database and Tableau.
Craig BucklerCraig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.
