Grouping Data

Group By Clause

• The GROUP BY clause divides the rows returned from the SELECT statement into groups.
• For each group, you can apply an aggregate function e.g., SUM() to calculate the sum of items or COUNT() to get the number of items in the groups.
• PostgreSQL evaluates the GROUP BY clause after the FROM and WHERE clauses and before the HAVING SELECT, DISTINCT, ORDER BY and LIMIT clauses.

SELECT
   column_1,
   column_2,
   ...,
   aggregate_function(column_3)
FROM
   table_name
GROUP BY
   column_1,
   column_2,
   ...;

select country, COUNT(country) as "number of brands" from product group by country order by country;

          country          | number of brands
---------------------------+--------
 CHINA                     |      1
 INDIA                     |      2
 NEPAL                     |      1
 USA                       |      2
(4 rows)

---

Having Clause

• The HAVING clause specifies a search condition for a group or an aggregate.
• The HAVING clause is often used with the GROUP BY clause to filter groups or aggregates based on a specified condition.
• PostgreSQL evaluates the HAVING clause after the FROM, WHERE, GROUP BY, and before the SELECT, DISTINCT, ORDER BY and LIMIT clauses.

SELECT
    column1,
    aggregate_function (column2)
FROM
    table_name
GROUP BY
    column1
HAVING
    condition;

select country, COUNT(country) as "Number of brand" from product group by country having Count(country) > 1 order by country;

          country          | Number of brand
---------------------------+-----------------
 INDIA                     |               2
 USA                       |               2
(2 rows)