PostgreSQL

Postgresql Count Function

The content present in the database always demands maintenance. This maintenance involves alteration of the table using the select, insert, and update command. In addition to this command, we need a function to help us provide totals of specified data present in the schema table. In the pgAdmin dashboard, it may be easy to gather the sum or aggregate of the total data. But in the case of the psql terminal, it is a difficult task. That’s why postgresql has introduced a function count(). It is somehow a simple built-in function but can be modified to the complex level by adding clauses in the statement.

The count () function “aggregate function” allows the user to fetch the number of rows that are matched with the specified requirement of the query, not only in the case of counting total data in a table. Specifically, the column and the rows having distinct values are major functionality of this function.

Syntax

The syntax of the count function is written so that there is a parameter present inside the parenthesis. This parameter determines the target point where the count function is to be applied.

Select COUNT(*) from table-name;

Select COUNT(column) from table-name;

Select COUNT(DISTINCT column) from table-name;

These are the basic syntax lines. These are enhanced by adding clauses with them. Each clause is separately explained ahead in this tutorial.

Count function ()

Consider a relation(table) named hospital with the following attributes: doctor id, name, city, salary, etc. Use “create” and “insert” statements to create the relationship and to insert all content. All the content of this table is displayed through the select statement.

>> select * from hospital;

We have taken a start with an easy example. In which we will get the total number of rows from the table hospital. For this purpose, use the simple count statement for the number of rows.

>> select count (*) from hospital;

This will display only the number of rows that are present in the table and not the whole content as it is a count function, so only the rows of the table are counted here. Further functions of count() are explained ahead in this tutorial.

Count for column

Sometimes such a scenario occurs when you need to count only the data present in any specific column. To avoid any confusion regarding the number of rows in all the tables, we will directly count the rows of the specific column by specifying the column name in the command because the total number of rows of the table doesn’t need to determine the same rows in each column. They can vary, depending upon the type of information the user has provided. Only you need to know about the column name of the table you are concerned with.

>> select * from software;

Moving forward, there we have table “software” having three columns. We want to display the number of rows in the column “versions”.

>> select count (versions) from software;

So the resultant value shows that there are only 3 rows in the “version” column, whereas the total rows in the column were 4. Hence, it is proved that the rows in each column may vary.

Count with distinct value

Again consider the table “hospital” for the new example. This example deals with fetching the number of rows from the column having distinct values. In other words, the rows that have the words dissimilar from the rest of the rows in the same column are fetched and counted.

This feature helps get the variation of data from using just a count() in the database. Otherwise, manual counting can charge a lot of time. Also, it shows that a single column may contain duplicate data irrespective of the specified ids.

Now, we will apply the command on the “city” column of the table. In this column, a city name is repeated more than once in a column. A “DISTINCT” keyword is used with the count command.

>> select count (DISTINCT city ) from hospital;

The rows obtained are 9. Whereas the total number of rows was 11. The city “Lahore” is repeated thrice in the column against different ids.

Count()  and GROUP BY

From the table “hospital”, we want to know about the number of doctors present. The count function will count the number of doctors in the hospital. Only the distinct ids will be counted individually. Here a new column named “ numbers of doctors” is created with the id column. Each id is counted, and the number is written in the same row in the new column along with each id.

>> select doc_id, count(*) AS “number of doctors” from hospital group by doc_id;

The image represents the number of ids in the table. i.e. ‘3’ is present twice in the table.

Count () and WHERE clause

This example is relevant to the previous one. In which we will take the same output but with another condition. This condition is applied to the column “salary”. Those doctors having their salaries greater than 30000 are displayed through the ids. And in the other column, their count is displayed.

>> select doc_id, count(*) AS “number of doctors” from hospital  WHERE salary > 30000 group by doc_id;

Count () and the HAVING clause

We want to get all the ids of the doctors having their salaries greater than 30000. After that, the result is further counted by the count () function. But only those ids are displayed whose answer of count() is equal or greater than 1.

>> select doc_id, count(*) AS “number of doctors” from hospital where salary > 30000 group by doc_id having count (*) > =1;

Firstly, the result is fetched by the salary column and then counted again for a further limited selection. The obtained output can be arranged in descending order depending on a single column.

Count (), GROUP BY, and ORDER BY clause

In this example, we will get the id and number of doctors, but the target column is not salary but the age column. Here the condition is applied to fetch ids of those doctors whose age is greater than 30. After fetching the total number of doctors against each id, the result is again sorted, and the total numbers equal to one or greater values are obtained. After that, these columns are arranged in descending order to the count () function we get.

>> select doc_id, count(*) AS “number of doctors” from hospital where age > 30 group by doc_id having count (*) > =1 order by count (*) desc;

From the output, you can see that 6 rows are obtained. Each number of count functions is displayed in front of the respective id.

Conclusion

The count () function is a better way of getting the total number of rows by applying requirement conditions. This function applies the WHERE, GROUP BY, and ORDER BY clause in the count () command. The resultant target is dependent on the parameter of the function. Each example is quoted, which is quite meaningful in its way of explaining the given concept.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.