PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - GROUPING SETS

GROUPING SETS is an advanced feature in PostgreSQL (and other SQL databases) that allows users to define multiple groupings in a single query. It provides more flexibility than the standard GROUP BY clause, enabling users to produce a single result set that's the union of multiple group-by clauses.

Using GROUPING SETS is often more concise and efficient than producing the same result set with multiple, separate queries (and then using UNION to combine the results).

Syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY GROUPING SETS (
    (grouping_column1, grouping_column2),
    (grouping_column3, grouping_column4),
    ...
);

Examples:

Suppose you have a sales table with columns year, product, and amount_sold.

Grouping by Multiple Sets:
If you want to see total sales by year, by product, and then by both year and product, you can do:
```
SELECT year, product, SUM(amount_sold) AS total_sold
FROM sales
GROUP BY GROUPING SETS (
    (year),
    (product),
    (year, product)
);
```
This will produce a result set that combines what would be three separate GROUP BY queries.
Using GROUPING SETS with CUBE and ROLLUP:
PostgreSQL also supports the CUBE and ROLLUP operations, which are shorthand for certain common kinds of GROUPING SETS.
Using ROLLUP on the previous example:
```
SELECT year, product, SUM(amount_sold) AS total_sold
FROM sales
GROUP BY ROLLUP (year, product);
```
The ROLLUP in this case is equivalent to:
```
GROUP BY GROUPING SETS (
    (year, product),
    (year),
    ()
);
```
Note: The empty grouping () is equivalent to a grand total.
Identifying the Level of Aggregation with GROUPING function:
PostgreSQL provides the GROUPING function, which can be used to identify the level of aggregation in the result:
```
SELECT year, product, SUM(amount_sold) AS total_sold, GROUPING(year) AS is_year_aggregated
FROM sales
GROUP BY GROUPING SETS (
    (year),
    (product),
    (year, product)
);
```
The GROUPING function returns 0 if the column is part of the grouping and 1 otherwise.

When to use `GROUPING SETS`:

When you want to see aggregated data at multiple levels of granularity in a single query.
When producing reports that require various summary levels.
When you want a more concise and often more efficient way than running multiple separate GROUP BY queries and then unioning the results.

Remember, while GROUPING SETS can be powerful, the result can also be a bit harder for humans to read and interpret because different rows in the result set represent data at different levels of granularity. Ensure you understand your result and possibly add columns to help clarify which rows are at which levels.

How to use GROUPING SETS in PostgreSQL:

Description: Group by multiple sets of columns using GROUPING SETS.

Code:

SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY GROUPING SETS ((department), (job_title), ());

PostgreSQL GROUPING SETS example:

Description: Basic example demonstrating the usage of GROUPING SETS.

Code:

SELECT region, country, city, AVG(population)
FROM cities
GROUP BY GROUPING SETS ((region), (country, city), ());

Multiple grouping sets in PostgreSQL:

Description: Group by multiple sets simultaneously.

Code:

SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY GROUPING SETS ((department), (job_title), (department, job_title), ());

GROUPING SETS vs. GROUP BY in PostgreSQL:

Description: Compare GROUPING SETS with traditional GROUP BY.

Code:

-- Using GROUP BY
SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY department, job_title;

-- Using GROUPING SETS
SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY GROUPING SETS ((department, job_title));

GROUPING SETS and ROLLUP/CUBE in PostgreSQL:
- Description: Understand the relationship between GROUPING SETS, ROLLUP, and CUBE.
- Code:
```
SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY ROLLUP (department, job_title);
```

ORDER BY with GROUPING SETS in PostgreSQL:

Description: Sort the result set of GROUPING SETS.

Code:

SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY GROUPING SETS ((department, job_title))
ORDER BY department, job_title;

Aggregation functions with GROUPING SETS in PostgreSQL:

Description: Use aggregation functions with GROUPING SETS.

Code:

SELECT department, job_title, AVG(salary), MAX(salary), MIN(salary)
FROM employees
GROUP BY GROUPING SETS ((department, job_title));

Dynamic SQL and GROUPING SETS in PostgreSQL:

Description: Use dynamic SQL to generate GROUPING SETS queries.

Code:

DO $$ 
DECLARE 
    sql_query TEXT;
BEGIN
    sql_query := 'SELECT department, job_title, AVG(salary)
                  FROM employees
                  GROUP BY GROUPING SETS ((department), (job_title), ())';
    EXECUTE sql_query;
END $$;

Using GROUPING SETS for complex aggregations in PostgreSQL:

Description: Perform complex aggregations using GROUPING SETS.

Code:

SELECT region, country, city,
       AVG(population), SUM(population), COUNT(*)
FROM cities
GROUP BY GROUPING SETS ((region), (country, city), ());

GROUPING SETS with multiple columns in PostgreSQL:

Description: Extend GROUPING SETS to multiple columns.

Code:

SELECT department, job_title, location,
       AVG(salary), MAX(salary), MIN(salary)
FROM employees
GROUP BY GROUPING SETS ((department, job_title), (location), ());

Combining GROUPING SETS with other clauses in PostgreSQL:

Description: Combine GROUPING SETS with WHERE, HAVING, and other clauses.

Code:

SELECT department, job_title, AVG(salary)
FROM employees
WHERE hire_date > '2022-01-01'
GROUP BY GROUPING SETS ((department), (job_title), ());

Handling NULLs with GROUPING SETS in PostgreSQL:

Description: Manage NULL values within GROUPING SETS.

Code:

SELECT department, job_title, AVG(salary)
FROM employees
GROUP BY GROUPING SETS ((department), (job_title), (NULL, job_title), ());

GROUPING SETS and window functions in PostgreSQL:

Description: Integrate GROUPING SETS with window functions for advanced analytics.

Code:

SELECT department, job_title, AVG(salary),
       RANK() OVER (PARTITION BY department ORDER BY AVG(salary) DESC) AS rank_within_department
FROM employees
GROUP BY GROUPING SETS ((department, job_title), ());

GROUPING SETS and subqueries in PostgreSQL:

Description: Use subqueries with GROUPING SETS for more complex analyses.

Code:

SELECT department, job_title, AVG(salary),
       (SELECT MAX(salary) FROM employees WHERE department = 'Sales') AS max_salary_sales
FROM employees
GROUP BY GROUPING SETS ((department, job_title), ());

Using GROUPING SETS for hierarchical data in PostgreSQL:

Description: Utilize GROUPING SETS for hierarchical data representation.

Code:

SELECT continent, country, city, AVG(population)
FROM cities
GROUP BY GROUPING SETS ((continent), (continent, country), (continent, country, city), ());

PostgreSQL - GROUPING SETS

Syntax:

Examples:

When to use GROUPING SETS:

When to use `GROUPING SETS`: