Introduction to Postgresql Count
There are many aggregate functions present in the PostgreSQL database. One of the aggregate function that is used to find the row count is COUNT() aggregate function. This function counts the total number of rows according to the query statement and clauses. When it is used on a particular column then only non-NULL values are considered. In this article we will see how does COUNT() function works with *, a particular column for nun-NULL values, DISTINCT keyword, GROUP BY clause and HAVING clause with the help of examples. We will begin studying and understanding the working of COUNT() function by learning its syntax. In this topic, we are going to learn about Postgresql Count.
Syntax
SELECT COUNT (* | [DISTINCT] ALL | columnName)
FROM tableName
[WHERE conditionalStatements];
The parameter that can be passed to count function can be either * that means all the rows to be counted in the resultset or column name with distinct or all keyword prepended to it. By default, it is ALL keyword when mentioned in a particular columnName. When the distinct keyword is used unique valued columns are considered in the resultset. The table name is the name of the table from which the result is to retrieved and row count to be found. ConditionalStatements are the conditions that you wish to apply in the where clause and are optional.
Example of Postgresql Count
Let us begin by connecting to out PostgreSQL database and open the psql terminal command- prompt using the following statements –
sudo su – postgres
psql
Enter the password if prompted.
The above queries will result in the access to Postgres command-prompt as follows –
Now let us create one table and insert values in it.
CREATE TABLE educba (technical_id serial PRIMARY KEY,technology_name VARCHAR (255) NOT NULL,course_Duration INTEGER,starting_date DATE NOT NULL DEFAULT CURRENT_DATE,department VARCHAR(100));
Firing the above query in our psql terminal command prompt will result in the following output –
Let us insert the value in the educba table without mentioning the starting_date column’s value while inserting.
INSERT INTO educba(technology_name, course_duration, starting_date, department) VALUES ('psql',35,'2020-04-07','Database');
This gives the following output –
Let’s insert some more entries –
INSERT INTO educba(technology_name, course_duration, department) VALUES ('mysql',40,'Database');
INSERT INTO educba(technology_name, course_duration, department) VALUES ('javascript',30,'scripting language');
INSERT INTO educba(technology_name, course_duration, department) VALUES ('java',35,'server- side language');
INSERT INTO educba(technology_name, course_duration, department) VALUES ('Angular',35,'Client-side language');
That results in the following output –
Let us now check the contents of our table educba by firing the following SELECT command –
SELECT * FROM educba;
That gives the following output –
Let us retrieve the row count of educba table using the COUNT() function. The query statement will be as follows –
SELECT COUNT(*) FROM educba;
That results in the following output –
Now, let us count the rows with 35 days of course_duration using the following query statement –
SELECT COUNT(*) FROM educba WHERE course_duration=35;
That results in the following output result –
As there are three rows with psql, java and angular as technology_name that have the course duration of 35 days, we got the row count as 3.
Using DISTINCT keyword
Whenever you want to get the unique valued row count of the particular column field then you can use the DISTINCT keyword in the SELECT clause. For example, suppose that we want to retrieve
How many departments are used in educba table then we can mention DISTINCT(department) in the SELECT clause using the following query statement –
SELECT COUNT(DISTINCT(department)) FROM educba;
That results in the following output-
Using GROUP BY clause
Now, let us retrieve the count of rows grouped according to the course_duration. Following will be the query statement that will be used to get the count of records grouped on basis of course_duration column –
SELECT COUNT(*),course_duration FROM educba GROUP BY course_duration;
Those output will be as follows –
As three technologies are having the course of duration as 35 and one technology count with 40 and 30 days duration each the above output is correct. But we are not able to know which technologies are considered in that count. To do so we can use GROUP_CONCAT() function.
Using string_agg function
The above query just retrieved the count of technologies grouped on course_duration used in the educba table. If we want the list of those technologies then we can use string_agg() function to get the comma-separated list of those technologies in the following way –
SELECT COUNT(technology_name) as technology_count, course_duration as duration_in_days ,string_agg(technology_name,',') as list_of_technologies FROM educba GROUP BY course_duration;
The output of the above query statement is as follows –
Retrieving column count
Firstly, let us add a new column to our educba table named temp_null_col column that will have the default null value in it. The alter table command is as follows –
alter table educba add column temp_null_col varchar default null;
And for verifying the records of educba we will fire the following command –
SELECT * from educba;
Whose output is as follows –
update educba set temp_null_col='temp' where department='Database';
Whose output is as follows –
SELECT * from educba;
That results in the following output –
Now, let us get the count of the column temp_null_col using the following query –
select count(temp_null_col) from educba;
Whose output is as follows –
As it can be seen that only non-null values are considered while counting the rows for the column temp_null_col, hence the output count is 2.
Conclusion
We can use COUNT() aggregate function in PostgreSQL to get the count of the number of rows of the particular query statement. Internally, first, the query is fired to obtain the resultset with all the rows that satisfy the condition and count the rows of the retrieved resultset to output the count value. COUNT() function can also be used on columns that help in retrieving the column count of non-null values.
It can also be used with the GROUP BY clause to get the count of grouped results. The unique valued count can be fetched by using the DISTINCT() function in the SELECT clause. We can use the string_agg() function to get the list of the column values of the columns other than the count parameter’s column to get the list of values considered in that count.
Recommended Articles
This is a guide to Postgresql Count. Here we discuss the query examples of Postgresql Count and open the psql terminal command- prompt using the statements. You may also have a look at the following articles to learn more –
