Aggregate Functions and Grouping in SQL

 

Aggregate Functions and Grouping in SQL

Aggregate functions in SQL are used to perform calculations on multiple rows of a table's column and return a single value. These are often combined with the GROUP BY clause to group rows that have the same values, and the HAVING clause to filter groups.

1. COUNT: Counting Rows

The COUNT() function returns the number of rows that match a specified condition.

Syntax:

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Example:

Let’s say you have a table called sales:

id	product	amount
1	Laptop	1000
2	Phone	600
3	Jacket	150
4	T-shirt	50
5	Phone	800

• To count the number of sales made:

  SELECT COUNT(*) AS total_sales
  FROM sales;
  

  Result:

  total_sales
  ------------
  5
  

2. SUM: Summing Values

The SUM() function calculates the total sum of a numeric column.

Syntax:

SELECT SUM(column_name)
FROM table_name
WHERE condition;

Example:

• To calculate the total sales amount:

  SELECT SUM(amount) AS total_revenue
  FROM sales;
  

  Result:

  total_revenue
  -------------
  2600
  

3. AVG: Calculating the Average

The AVG() function returns the average value of a numeric column.

Syntax:

SELECT AVG(column_name)
FROM table_name
WHERE condition;

Example:

• To calculate the average sales amount:

  SELECT AVG(amount) AS avg_sales
  FROM sales;
  

  Result:

  avg_sales
  ---------
  520
  

4. MIN and MAX: Finding the Minimum and Maximum

• MIN() returns the smallest value in a column.
• MAX() returns the largest value in a column.

Syntax:

SELECT MIN(column_name), MAX(column_name)
FROM table_name
WHERE condition;

Example:

• To find the minimum and maximum sales amounts:

  SELECT MIN(amount) AS min_sale, MAX(amount) AS max_sale
  FROM sales;
  

  Result:

  min_sale | max_sale
  -------------------
  50       | 1000
  

5. GROUP BY: Grouping Data

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregate functions to perform operations on each group of rows.

Syntax:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE condition
GROUP BY column_name;

Example:

• To calculate the total sales amount for each product:

  SELECT product, SUM(amount) AS total_sales
  FROM sales
  GROUP BY product;
  

  Result:

  product | total_sales
  ---------------------
  Laptop  | 1000
  Phone   | 1400
  Jacket  | 150
  T-shirt | 50
  

Here, the GROUP BY groups rows by product and SUM(amount) calculates the total for each group.

6. HAVING: Filtering Grouped Data

The HAVING clause is used to filter the result of GROUP BY based on conditions applied to aggregated data. It is similar to the WHERE clause but operates on groups rather than individual rows.

Syntax:

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

Example:

• To get products that have total sales greater than 500:

  
  SELECT product, SUM(amount) AS total_sales
  FROM sales
  GROUP BY product
  HAVING SUM(amount) > 500;
  

  Result:

  product | total_sales
  ---------------------
  Laptop  | 1000
  Phone   | 1400
  

In this case, only products with total sales greater than 500 are returned.