Aggregate Functions in MySQL Workbench

• Aggregate functions in MySQL are used to perform calculations on groups of rows and return a single result value for each group. These functions are commonly used with the `GROUP BY` clause to group rows based on certain columns and then apply the aggregate functions to those groups.

Here's a detailed explanation of various MySQL aggregate functions, along with examples:

• COUNT() Function: Counts the number of rows in a result set or the number of non-null values in a specified column.

    SELECT COUNT(*) FROM orders; -- Count all rows

    SELECT COUNT(product_id) FROM products; -- Count non-null product IDs

• SUM() Function: Calculates the sum of numeric values in a specified column.

    SELECT SUM(price) FROM products; -- Sum of all product prices

• AVG() Function: Calculates the average (mean) of numeric values in a specified column.

    SELECT AVG(price) FROM products; -- Average price of products

• MIN() Function: Retrieves the minimum value in a specified column.

    SELECT MIN(price) FROM products; -- Minimum price among products

• MAX() Function: Retrieves the maximum value in a specified column.

    SELECT MAX(price) FROM products; -- Maximum price among products

• GROUP_CONCAT() Function: Concatenates values from multiple rows into a single string within a group.

    SELECT customer_id, GROUP_CONCAT(order_id) FROM orders GROUP BY customer_id;

    -- Concatenates order IDs for each customer

• HAVING Clause: Works with aggregate functions to filter groups based on conditions.

    SELECT customer_id, COUNT(order_id) FROM orders GROUP BY customer_id HAVING COUNT(order_id) > 2;

    -- Customers with more than 2 orders

• STD() and VARIANCE() Functions: Calculate the standard deviation and variance of numeric values.

    SELECT STD(price) FROM products; -- Standard deviation of product prices

    SELECT VARIANCE(price) FROM products; -- Variance of product prices

• SUM() with DISTINCT: You can use `SUM()` with `DISTINCT` to calculate the sum of unique values.

    SELECT SUM(DISTINCT price) FROM products; -- Sum of distinct product prices

• BIT_AND() and BIT_OR() Functions: Perform bitwise AND and OR operations on values within a group.

    SELECT BIT_AND(permission) FROM user_permissions; -- Bitwise AND of permissions

    SELECT BIT_OR(permission) FROM user_permissions; -- Bitwise OR of permissions

• These aggregate functions help you derive meaningful insights from your data by performing calculations across groups of rows. When used in combination with the `GROUP BY` clause, they allow you to summarize and analyze data more effectively.