top of page
hand-businesswoman-touching-hand-artificial-intelligence-meaning-technology-connection-go-

SQL PIE-CHART

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!

21 views0 comments

Recent Posts

See All

Commenti

Valutazione 0 stelle su 5.
Non ci sono ancora valutazioni

Aggiungi una valutazione
bottom of page