_edited_edited.png)
Pie-Charts are graphical representation of data in the form of a circular chart with each slice representing a proportionate part of the whole. All slices of the pie add up to make the whole equaling to 100 percent and 360 degrees. Refer to the example given below:
Pie-Charts are simple to understand and visualize the composition of a set of data. The slices of the pie-chart are often represented in the form of percentages, representing their contribution to the whole. To create a pie-chart, the data and categories must be subsets of a larger data group of a single category.
In this blog, we’ll see creation of a Pie-Chart representing patients and their total number of symptoms. The set of codes provided is written in PostgreSQL and executed in a Postgres server using pgAdmin 4 tool.
Let’s get started:
Table Creation:
We’ll create a pie-chart that will represent the number of Covid symptoms suffered by the number of patients i.e. one covid symptom shown by how many patients, two covid symptoms shown by how many patients and so on.
SQL Code :
I have divided my code into parts and given a code walk-through with detailed explanation.
\set width 50
\set height 20
\set radius 1.0
\set colours ‘’’#:o;x”@+-=12'’’
The \set commands here are used to assign values to local variables to set the width, height and radius of the circle of the pie-chart.
\set colours here represents the symbols of each slice to be filled within the pie-chart.
WITH slices AS
(SELECT CAST(
row_number() over () AS integer) AS slice,name,value,100.0 value /
sum(value) OVER () AS percentage,
2PI() * sum(value) OVER (rows unbounded preceding) / sum(value) OVER ()
AS radians FROM
Here,
Percentage = 100 * no. of patients with [1] symptom/ total no. of patients
Percentage = 100 * no. of patients with [2] symptoms/ total no. of patients
and so on…
Radian (angle of the slice) = 2 PI() sum of no. of patients with [1] symptom from the first row to the current row / sum of the total no. of patients
Radian (angle of the slice) = 2 PI() sum of no. of patients with [2] symptoms from the second row to the current row / sum of the total no. of patients
and so on…
(select (fever + cough + headache + bodyache + nasal_congestion +
shortness_of_breath + loss_of_smell_taste
) as Noofsymptoms,
count (*) as NoofPatients from patient_covidsymptoms group by
(fever + cough + headache + bodyache + nasal_congestion + shortness_of_breath + loss_of_smell_taste))
AS data(name,value))
This piece of code will give the total number of symptoms shown by the number of patients.
Example:
Fever: 1 symptom shown by say 2 patients
Fever + Cough : 2 symptoms shown by say 4 patients
Fever + Cough + Shortness of breath : 3 symptoms shown by say 8 patients and so on.
Below table is the result of the execution of the above code:
(SELECT array_to_string(array_agg(c),’’) AS pie_chart
FROM (SELECT x, y,
CASE WHEN NOT (sqrt(pow(x, 2) + pow(y, 2)) BETWEEN 0.0 AND :radius)
THEN ‘ ‘
ELSE substring(:colours,
(select min(slice) from slices where radians >= PI() + atan2(y,-x)), 1)END AS cFROM(SELECT 2.0*generate_series(0,:width)/:width-1.0) AS x(x),
(SELECT 2.0*generate_series(0,:height)/:height-1.0) AS y(y)
ORDER BY y,x) AS xy
GROUP BY y
ORDER BY y
)
With the help of the above piece of code, a pie chart will be prepared by creating a string array with the required number of rows and columns. Example: (x,y) = (50X20)
Every (x,y) point is selected in the array and then the length of the line joining this point to the center of the array is found out using the sqrt() and the pow() function.
If the length of this line is greater than the radius of the circle i.e. 1, that means that point falls outside the circle. That same point will then be replaced with a blank space
ELSE if the point falls within the circle, then the angle of the point from the x-axis will be found out using the atan2() function.
Also, in which slice the point falls is found out next.
That point is then replaced with the character that is assigned for that slice in the pie-chart.
(SELECT 2.0*generate_series(0,:width)/:width-1.0) AS x(x),
(SELECT 2.0*generate_series(0,:height)/:height-1.0) AS y(y)
The above code generates a series of points with (0,0) being the center and all the end points being (1,0), (-1,0), (0,1), (0,-1) . These co-ordinates falls on the circumference of the circle as the radius of the circle is 1.
In this example, 50x20 points will be generated in this array.
UNION ALL
SELECT repeat(substring(:colours,slice,1), 2) || ‘ ‘ ||
name || ‘: ‘ ||
value || ‘ (‘ || round(percentage,0) || ‘%)’
FROM slices;
The code here will display the characters used for filling each pie-slice, the number of symptoms each patient had, the number of patients who had either 1 symptom or 2 symptoms or 3 symptoms, so on, and the percentage of each slice in the pie-chart.
Complete SQL Code:
\set width 50
\set height 20
\set radius 1.0
\set colours ‘’’#:o;x”@+-=12'’’
WITH slices AS (
SELECT CAST(
row_number() over () AS integer) AS slice,name,value,100.0 value /
sum(value) OVER () AS percentage,
2PI() sum(value) OVER (rows unbounded preceding) / sum(value) OVER ()
AS radians FROM
(select (fever + cough + headache + bodyache + nasal_congestion + shortness_of_breath + loss_of_smell_taste) as Noofsymptoms,
count () as NoofPatients from patient_covidsymptoms
group by(fever + cough + headache + bodyache + nasal_congestion + shortness_of_breath + loss_of_smell_taste))
AS data(name,value))
(SELECT array_to_string(array_agg(c),’’) AS pie_chart
FROM (SELECT x, y,
CASE WHEN NOT (sqrt(pow(x, 2) + pow(y, 2)) BETWEEN 0.0 AND :radius)
THEN ‘ ‘
ELSE substring(:colours,
(select min(slice) from slices where radians >= PI() + atan2(y,-x)), 1)
END AS c
FROM(SELECT 2.0*generate_series(0,:width)/:width-1.0) AS x(x),
(SELECT 2.0*generate_series(0,:height)/:height-1.0) AS y(y)
ORDER BY y,x) AS xy
GROUP BY y
ORDER BY y
)
UNION ALL
SELECT repeat(substring(:colours,slice,1), 2) || ‘ ‘ ||
name || ‘ : ‘ ||
value || ‘ : ‘ ||
round(percentage,0) || ‘%’
FROM slices;
Execution of the Code :
In pgAdmin, right click on the database and select PSQL Tool. Copy-paste the above code here and execute.
Conclusion :
Hope my SQL PIE-CHART Blog will be of some help to you in creating pie-charts in PostgreSQL.
Thank you!