How do I count unique values in PostgreSQL?

Postgresql database, like other databases, can store data that remain intact for a long time. This can be done by removing redundancy from the data in the schema by maintaining concurrency and accuracy of data present. While adding data in relations, you might encounter such a situation in which you don’t notice the replication of the inserted data at the time of inserting. To remove the garbage values or similar data, we need some special functions to keep the rest of the values unique and distinct. Count () is a built-in function of PostgreSQL. By using this function, we can get information regarding the data present in the table. Postgresql deals with the count () very effectively by displaying data to the provided conditions. To get the distinct data or the values in terms of discrimination with other data, we need some different commands and the count (). This article will highlight the examples applied on different relations to elaborate the concept of counting unique values in PostgreSQL.

Firstly, you need to create a database in the installed PostgreSQL. Otherwise, Postgres is the database that is created by default when you start the database. We will use psql to start implementation. You may use pgAdmin.

A table named “items” is created by using a create command.

>> create table items ( id integer, name varchar(10), category varchar(10), order_no integer, address varchar(10), expire_month varchar(10));

To enter values in the table, an insert statement is used.

>> insert into items values (7, ‘sweater’, ‘clothes’, 8, ‘Lahore’);

After inserting all data through the insert statement, you can now fetch all the records through a select statement.

>> select * from items;

Example 1
This table, as you can see from the snap, has some similar data in each column. To distinguish the uncommon values, we will apply the “distinct” command. This query will take a single column, whose values are to be extracted, as a parameter. We want to use the first column of the table as input of the query.

>> select distinct (id) from items order by id;

From the output, you can see that the total rows are 7, whereas the table has 10 rows total, which means some rows are deducted. All the numbers in the “id” column that were duplicated twice or more are displayed only once to distinguish the resultant table from others. All the result is arranged in the ascending order by the use of “order clause”.

Example 2
This example is related to the subquery, in which a distinct keyword is used within the subquery. The main query selects the order_no from the content obtained from the subquery is an input for the main query.

>> select order_no from (select distinct ( order_no) from items order by order_no) as foo;

The subquery will fetch all the unique order numbers; even repeated ones are displayed one time. The same column order_no again orders the result. At the end of the query, you have noticed the use of ‘foo’. This acts as a placeholder to store the value that can change according to the given condition. You can also try without using it. But to assure the correctness, we have used this.

Example 3
To get the distinct values, here we another method to make use of. The “distinct” keyword is used with a function count (), and a clause that is “group by”. Here we have selected a column named “address”. The count function counts the values from the address column that are obtained through the distinct function. Besides the query result, if we randomly think to count the distinct values, we will come with a single value for each item. Because as the name indicates, distinct will bring the values one either they are present in numbers. Similarly, the count function will display only a single value.

>> select address, count ( distinct(address)) from items group by address;

Each address is counted as a single number because of distinct values.

Example 4
A simple “group by” function determines the distinct values from two columns. The condition is that the columns you have selected for the query to display the contents must be used in the “group by” clause because the query will not work properly without that.

>> select id, category from items group by category, id order by 1;

All the resultant values are organized in ascending order.

Example 5
Again consider the same table with some alteration in it. We have added a new layer to apply some constraints.

>> select * from items;

The same group by and the order by clauses is used in this example applied to two columns. Id and the order_no are selected, and both are grouped by and ordered by 1.

>> select id, order_no from items group by id, order_no order by 1;

As each id has a different order number except one number that is newly added “10”, all other numbers that have twice or more presence in the table are displayed simultaneously. For instance, “1” id has order_no 4 and 8, so both are mentioned separately. But in the case of “10” id, it is written one time because both the ids and the order_no are the same.

Example 6
We have used the query as mentioned above with the count function. This will form an additional column with the resultant value to display the count value. This value is the number of times both “id,” and the “order_no” are the same.

>> select id, order_no, count(*) from items group by id, order_no order by 1;

The output shows that each row has the count value of “1” as both have a single value that is dissimilar from one another except the last one.

Example 7
This example uses almost all the clauses. For instance, the select clause, group by, having clause, order by clause, and a count function are used. Using the “having” clause, we can also get duplicate values, but we have applied a condition with the count function here.

>> select order_no from items group by order_no having count (order_no) > 1 order by 1;

Only a single column is selected. First of all, the values of order_no that are distinct from other rows are selected, and the count function is applied to it. The resultant that is obtained after the count function is arranged in ascending order. And all the values are then compared with the value “1”. Those values of the column greater than 1 are displayed. That’s why from 11 rows, we get only 4 rows.

Conclusion

“How do I count unique values in PostgreSQL” has a separate working than a simple count function as it can be used with different clauses. To fetch the record having a distinct value, we have used many constraints and the count and distinct function. This article will guide you on the concept of counting the unique values in the relation.

How do I count unique values in PostgreSQL?

Conclusion

About the author

Aqsa Yasin