Aggregate Functions
In SQL, aggregate functions such as MIN, MAX, COUNT, SUM, AVG, STD, VAR, ABS, SQUARE, and SQRT are used to perform calculations on a set of values in a column.
1. MIN:
Returns the smallest value in a column.
SELECT MIN(column_name)
FROM table_name;
2. MAX:
Returns the largest value in a column.
SELECT MAX(column_name)
FROM table_name;
3. COUNT:
Returns the number of rows in a table or the number of non-null values in a column.
SELECT COUNT(*)
FROM table_name; -- Count all rows
SELECT COUNT(column_name)
FROM table_name; -- Count non-null values in a column
SELECT COUNT(DISTINCT column_name)
FROM table_name; -- Count the number of unique rows
4. SUM:
Returns the total sum of values in a column.
SELECT SUM(column_name)
FROM table_name;
5. AVG:
Returns the average value of a column.
SELECT AVG(column_name)
FROM table_name;
6. STD:
Returns the standard deviation of the values in a column.
SELECT STD(column_name)
FROM table_name;
7. VAR:
Returns the variance of the values in a column.
SELECT VAR(column_name)
FROM table_name;
8. ABS:
Returns the absolute value of a given number.
SELECT ABS(column_name)
FROM table_name;
9. SQUARE:
Returns the square of the values in a column.
SELECT SQUARE(column_name)
FROM table_name;
9. SQRT:
Returns the square root of the values in a column.
SELECT SQRT(column_name)
FROM table_name;
Group By
In SQL, the GROUP BY clause is used to group rows that have the same values into summary rows, typically for use with aggregate functions to perform operations on each group.
Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;
column1: The column(s) by which you want to group the result set.aggregate_function: The aggregate function to perform on each group.
Example:
Let's say we have a table named orders with columns product_id, customer_id, and quantity.
To count the number of orders for each product, you can use GROUP BY with the COUNT aggregate function:
SELECT product_id, COUNT(*) AS num_orders
FROM orders
GROUP BY product_id;
Additionally, you can use GROUP BY with multiple columns to create groups based on multiple criteria. For example:
SELECT product_id, customer_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY product_id, customer_id;
This query groups the data by both product_id and customer_id, and calculates the total quantity of each product ordered by each customer.
Having Clause
In SQL, the HAVING clause is used in combination with the GROUP BY clause to filter the result set based on aggregated values. While the WHERE clause filters individual rows before the grouping operation, the HAVING clause filters groups of rows after the grouping operation.
Syntax:
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
column1: The column(s) by which you want to group the result set.aggregate_function: The aggregate function to perform on each group.condition: The condition to filter groups.
Example:
Suppose we have a table named orders with columns product_id, customer_id, and quantity.
To find products with total orders greater than a certain threshold (let's say 100 units), you can use GROUP BY with HAVING:
SELECT product_id, SUM(quantity) AS total_quantity
FROM orders
GROUP BY product_id
HAVING SUM(quantity) > 100;
