Blog

Grouping data is one of the most important tasks in SQL. The PostgreSQL GROUP BY Statement in SQL is used to arrange identical data into groups with the help of the group functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.

PostgreSQL group by :

GROUP BY clause is used with the SELECT statement.
GROUP BY clause is placed after the WHERE clause.
GROUP BY clause is placed before ORDER BY clause if used any.
Make sure that all columns in the select list that are not group functions are included in the group by function.

Syntax

SELECT column1, column2
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2
ORDER BY column1, column2

Example.,

select job, avg(sal) average_salary from emp group by job;

NOTE: Group By multiple columns: Group by multiple columns is said for example, GROUP BY column1, column2. This means to place all the rows with same values of both the columns column1 and column2 in one group.

Restricting group by results with the having clause

PostgreSQL Having clause:

We know that WHERE clause is used to place conditions on columns but what if we want to place conditions on groups?

This is where HAVING clause comes into use. We can use postgreSQL HAVING clause to place conditions to decide which group will be the part of final result-set. Also, we can not use the aggregate functions like SUM(), COUNT() etc. with WHERE clause. So we have to use HAVING clause if we want to use any of these functions in the conditions.

In the above example, what if we want to get the employees whose department average salary is greater than 2800.

Test your understanding

The following query is written to retrieve all those product IDs from the SALES table that have more than 55000 sold and have been ordered more than 10 times.

SQL>select prod_id from sales

where quantity_sold>55000 and count(*)>10

group by prod_id

having count(*) > 10

Which statement is true regarding this SQL statement?
A. It executes successfully and generates the required result.
B. It produces an error because count(*) should be specified in the SELECT clause also.
C. It produces an error because count{*) should be only in the HAVING clause and not in the WHERE clause.
D. It executes successfully but produces no result because COUNT (prod_id) should be used instead of COUNT (*).

Answer: C

Explanation:

Restricting Group Results with the HAVING Clause
You use the HAVING clause to specify the groups that are to be displayed, thus further restricting the groups on the basis of aggregate information.
In the syntax, group_condition restricts the groups of rows returned to those groups for which the specified condition is true.

The Oracle server performs the following steps when you use the HAVING clause:
1. Rows are grouped.
2. The group function is applied to the group.
3. The groups that match the criteria in the HAVING clause are displayed.
The HAVING clause can precede the GROUP BY clause, but it is recommended that you place the GROUP BY clause first because it is more logical. Groups are formed and group functions are calculated before the HAVING clause is applied to the groups in the SELECT list.

Note: The WHERE clause restricts rows, whereas the HAVING clause restricts groups.

Words from postgreshelp

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook | Twitter
Please subscribe to our newsletter.