_edited_edited.png)
PostgreSQL is one of the open-source relational database management system. One of the features of PostgreSQL, Programmers can communicate with database servers using objects in their code.
The main difference between PostgreSQL and SQL, PostgreSQL is designed run on multiple operating system including Linux, macOS, and Windows while SQL server is primarily designed to run on Windows. Also, PostgreSQL is recommended for large analytical processes. In PostgreSQL we can define our own data types, build out custom function and so on.
Aggregate function
PostgreSQL supports aggregate function. An aggregate function computes a single result from multiple input rows. For Example, there are aggregates to compute the count, sum, avg(average), max(maximum) and min(minimum) over a set of rows.
Average() function in PostgreSQL
In PostgreSQL One of the aggregate function in PostgreSQL is Average(). Using this average() function we can able to get the average calculation from the data.
From below examples, we are going to use two different tables. to get the average and percentage of the patients values. First to find the average for the patients Apgar score using avg() function. This Apgar scoring system provided a standardized assessment for infants after delivery. Normal Apgar score between 7,8,or 9. Here we are going to get average Apgar score for the new born patients.
First we are going to create the table in the name of hospitalization_labor using create table statement like below.
By using create statement to create the table called hospitalization_labor, the output will look like above with the output message 'CREATE TABLE' and 'Query returned successfully'.
Then we have to add the values for all the columns like below with the query insert into statement
After inserting the values to the hospitalization_labor table the screen will show the output of the table like above with the message 'Query returned successfully in 75 msec'.
To check the result by using select statement for the the above table
The above picture shows the data output for the table hospitalization_labor with columns and values.
Avg() function with single column in PostgreSQL
For single column we can simply use Average function by using avg(column name) to get the average.
To get the average of the Apgar score for the patients. Here is the single column avg() function looks like
 Â
select avg(apgar_1st) from hospitalization_labor
Using select statement we can get the average of apgar patients from the table and the output likes above.
But here the output shows with too many decimals. To avoid to get only one or two digit values we can use the round function with the avg() function like below
The above picture shows the output without decimals.
Avg() function with multiple column in PostgreSQL
We can achieve average for multiple column by using the select statement query with unnest function in PostgreSQL like below
select *,(select avg(x) from unnest(array[apgar_1st,apgar_2nd]) as x) as avgapgar
from hospitalization_labor
Above picture shows the average of the Apgar patients from the multiple column values. Like this we can able to find the averages for single and multiple column values in PostgreSQL.
Percentage in PostgreSQL
To get the percentage of the single and multiple columns in PostgreSQL is little different from the AVG() function in PostgreSQL. Here we are going to use another table called  patient_fat_assmt to get the percentage of the patients fat. This table has different types of fat assessment of the patients.
First we are creating a table using create statement like below
create table patient_fat_assmt(
patient_id integer,
periumbilical_fat integer,
periumbilical_total_fat integer,
periumbilical_visceral_fat integer
)
The above pictures shows the output with the message of 'create table' and 'Query returned successfully'.
Then adding the values for the above table by using insert into statement with the values like below.
insert into patient_fat_assmt(patient_id,periumbilical_fat,periumbilical_total_fat,periumbilical_visceral_fat)
values(1001,15.2,4.3,12.5),
(1002,18.9,14.3,10.5),
(1005,11.2,8.3,15.7),
(1007,23.4,7,34),
(1004,43.5,6,67),
(1006,17.9,5,13.2),
(1009,14.8,1.5,12.3),
(1025,15.5,0,13.5),
(1015,14.4,4,13.7),
(1035,15.6,1.4,12.8)
After insert into query the output of the table with the message 'insert' and 'Query returned successfully'.
To check the result by using select statement for the the above table
This select statement query returns the output with all the patients values. Next we are going to see how to get the percentage of single column value.
Percentage for one column in PostgreSQL
To find Percentage for single column using select statement with cross join like below
select patient_id,round(100*periumbilical_total_fat/fatsum.sum,1) as percent
from patient_fat_assmt
cross join(
select sum(periumbilical_total_fat) from patient_fat_assmt) as fatsum
order by percent
This above query returns the output of the patients percentage with the single column.
Percentage for multiple column in PostgreSQL
Next, we are going to see how to get the percentage of the patients with multiple columns in PostgreSQL
By using below select statement we can able to get the result,
select patient_id,round(100*multiperc/
sum(multiperc )over ( ),1) as percentile
from (
select patient_id,sum(periumbilical_total_fat+periumbilical_fat+periumbilical_visceral_fat/3) as multiperc
from patient_fat_assmt
group by patient_id)
order by percentile
In conclusion,The PostgreSQL functions are very useful for getting aggregated values for all types of data's. Postgres allows us to compute the average of distinct values using an option Distinct. While calculating the average of a set, the AVG() function skips the ‘NULL’ values.
Â
Â
References
Online articles and resources:
Thank you for reading my blog and see you with another interesting blog!!!.
Comments