Count, Sum & Average In MySQL

In MySQL, we can use aggregate functions like "COUNT", "SUM", and "AVG" to perform calculations on sets of rows.

COUNT, AVG, and SUM aggregator functions are combined with the GROUP BY clause to perform calculations on groups of rows.

COUNT In MySQL:

The COUNT aggregate function counts the number of rows that meet a specified condition in a table.

Example:

SELECT COUNT(*) AS total_products
FROM products
WHERE category = 'GAMES';

Note: The above query counts the total number of products in the `products``category` column is equal to 'GAMES' in the Table.

SUM In MySQL:

The SUM function is used to calculate the sum of values in a numeric column.

Example:

SELECT SUM(product_price) AS Total_Products_Price 
FROM products;

Note: The above query calculates all product Prices exist in the `products` Table.

Average In MySQL:

The AVG function is used to calculate the average of values in a numeric column.

Example:

SELECT AVG(price) AS average_price
FROM products
WHERE category = 'GAMES';

Note: The above query calculates the average price of all products available in the `products` where `category` is equal to 'GAMES'.

Aggregate Function without WHERE Clause:

These aggregate functions can also be used without a WHERE clause to perform calculations on all rows in a table.

Example:

-- Total number of products
SELECT COUNT(*) AS total_products
FROM products;

-- Total salary of all products
SELECT SUM(price) AS total_price
FROM products;

-- Average salary of all products
SELECT AVG(price) AS average_price
FROM products;

Aggregate Function With Group By In MySQL:

COUNT with GROUP BY In MySQL:

The below query returns the total count of products available in each category.

Example:

SELECT COUNT(*) AS Total_Products_Category_Wise, category
FROM products
GROUP BY category;

AVG with GROUP BY In MySQL:

The below query returns the average price of products available in each category.

Example:

SELECT AVG(product_price) AS average_price_category_wise, category
FROM products
GROUP BY category;

SUM with GROUP BY In MySQL:

The below query returns the total amount of products for each category.

Example:

SELECT SUM(product_price) AS total_amount_category_wise, category
FROM products
GROUP BY category;

<-- Aliases

AND & OR -->