In SQL, we can use aggregate functions like "COUNT", "SUM", and "AVERAGE" 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.
Here is how these functions can be used:
The COUNT aggregate function is used to count the number of rows that meet a specified condition in a table.
Note: The below query counts the total number of products in product table in the 'GAMES' category.
SELECT COUNT(*) AS total_products FROM products WHERE category = 'GAMES';
The SUM function is used to calculate the sum of all values in a numeric column in a table.
Note: The below query calculates the sum of `Product Price` of all Products in the Table.
SELECT SUM(product_price) AS Total_Products_Price FROM products;
The AVG function is used to calculate the average of values in a numeric column.
The below query calculates the average price of all products in the 'GAMES' category.
SELECT AVG(product_price) AS average_price FROM products WHERE category = 'GAMES';
These aggregate functions can also be used without a WHERE clause to perform calculations on all rows in a table.
-- 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;
This query returns the count of products in each category.
SELECT COUNT(*) AS Total_Products_Category_Wise, category FROM products GROUP BY category;
This query returns the average product price in each category.
SELECT AVG(product_price) AS average_price_category_wise, category FROM products GROUP BY category;
This query returns the total product amount for each category.
SELECT SUM(product_price) AS total_amount_category_wise, category FROM products GROUP BY category;