Create Frequency table in PostgreSQL

In this section we will see how to create frequency table in PostgreSQL. To get frequency table in  PostgreSQL, you can use the GROUP BY clause along with the HAVING.  These methods are explained with examples.

• Create frequency table in PostgreSQL using single column
• Create frequency table in PostgreSQL using multiple columns
• Create the frequency table of unique rows of the PostgreSQL table

The table which we use for depiction is

examscore_dup:

Get frequency table in PostgreSQL based on the single column:

To get frequency table based on a single column, such as name, you can use the following query

 

SELECT name, COUNT(*)

FROM examscore_dup

GROUP BY name

This query groups the rows by the name column and counts the number of occurrences for each name.  which will in turn creates the frequency table based on the name column.

Output:

In  order to get the Frequency table based on the count of occurrence we can use ORDER BY  &  DESC keywords which will rearrange the frequency table in descending order of the count of occurrence

 

SELECT name, COUNT(*) as frequency

FROM examscore_dup

GROUP BY name

order by frequency DESC

Output:

Get frequency table in PostgreSQL based on the multiple column:

To get frequency table based on multiple columns, such as name and studentid, you can use a similar approach:

 

SELECT studentid,name, COUNT(*)

FROM examscore_dup

GROUP BY studentid,name;

This query groups the rows by both the name and studentid columns and counts the number of occurrences for each combination. Which will result in frequency table of multiple columns (name and studentid columns).

Output:

In  order to get the Frequency table based on the count of occurrence we can use ORDER BY  &  DESC keywords which will rearrange the frequency table in descending order of the count of occurrence

 

SELECT studentid,name, COUNT(*) as frequency

FROM examscore_dup

GROUP BY studentid,name

ORDER BY frequency DESC;

Output:

Frequency Table of All Columns in PostgreSQL: (Get Frequency table of unique rows)

If you want to count the frequency of each unique row (considering all columns), you can use the following query, This query gets the frequency table of unique rows of the PostgreSQL table

 


SELECT studentid,name,sciencescore,mathematicsscore,language1score,language2score, COUNT(*) AS frequency

FROM examscore_dup

GROUP BY studentid,name,sciencescore,mathematicsscore,language1score,language2score

ORDER BY frequency DESC;

This query groups the rows by all columns and counts the occurrences of each unique row.

Output: