SQL : Aggregating data in SQL

PostgreSQL provides a variety of aggregation functions that allow you to compute a single result from multiple input rows. Below are some of the most commonly used aggregation functions:

  1. COUNT()
    Counts the number of rows. sql SELECT COUNT(*) FROM users;

  2. SUM()
    Calculates the sum of a numeric column. sql SELECT SUM(salary) FROM employees;

  3. AVG()
    Returns the average (mean) of a numeric column. sql SELECT AVG(salary) FROM employees;

  4. MIN()
    Returns the smallest value of a column. sql SELECT MIN(salary) FROM employees;

  5. MAX()
    Returns the largest value of a column. sql SELECT MAX(salary) FROM employees;

  6. ARRAY_AGG()
    Aggregates values from multiple rows into a single array. sql SELECT ARRAY_AGG(salary) FROM employees;

  7. STRING_AGG()
    Concatenates strings from multiple rows into a single string, with an optional separator. sql SELECT STRING_AGG(name, ', ') FROM users;

  8. BOOL_AND()
    Returns TRUE if all input values are true. sql SELECT BOOL_AND(is_active) FROM users;

  9. BOOL_OR()
    Returns TRUE if at least one input value is true. sql SELECT BOOL_OR(is_active) FROM users;

  10. VARIANCE() / VAR_SAMP()
    Calculates the sample variance of a numeric column. sql SELECT VARIANCE(salary) FROM employees;

  11. STDDEV() / STDDEV_SAMP()
    Calculates the sample standard deviation of a numeric column. sql SELECT STDDEV(salary) FROM employees;

  12. MODE()
    Returns the most frequent value in a column. sql SELECT MODE() WITHIN GROUP (ORDER BY salary) FROM employees;

  13. PERCENTILE_CONT()
    Calculates a continuous percentile value within a group of rows. sql SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

  14. PERCENTILE_DISC()
    Returns the discrete percentile value within a group. sql SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary) FROM employees;

These functions are typically used with the GROUP BY clause to group rows before applying the aggregation function.

Explain GROUP BY

In PostgreSQL, the GROUP BY clause is used to aggregate data based on one or more columns. It allows you to group rows that have the same values in specified columns and perform aggregate functions (such as COUNT, SUM, AVG, MIN, MAX) on each group.

Basic Syntax

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

Key Points

  • You can group by one or more columns.
  • Any column not in an aggregate function must be included in the GROUP BY clause.
  • Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), MAX().

Example 1: Grouping by a Single Column

Suppose you have a table employees with columns department, salary, and you want to find the average salary for each department.

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

This query will return the average salary for each department.

Example 2: Grouping by Multiple Columns

If you want to find the total salary by both department and job_title, you can group by both columns:

SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY department, job_title;

Example 3: Using HAVING with GROUP BY

The HAVING clause is used to filter groups based on aggregate functions. For example, to show only departments where the average salary is greater than 50,000:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

This query will return departments where the average salary is more than 50,000.

Let me know if you need a specific example based on your use case!

links

social