SQL Aggregate Functions for beginners

SQL AGGREGATE FUNCTIONS Data aggregation is the process of taking several rows of data and condensing them into a single result or summary. When dealing with large datasets, this is invaluable because it allows you to extract relevant insights without having to scrutinize each individual data point. So, what exactly are SQL aggregate functions? They are specialized functions that perform calculations on groups of variables and return a single result. Unlike traditional functions, aggregate functions work on groups of rows of data. This allows you to efficiently compute statistics or generate summary information from a dataset. In this article, we will look at the importance of SQL aggregate functions and how to use them. We’ll explain them using real-world examples. Let's explore each of them below; COUNT() The COUNT() functions objective is to count the number of rows in a table or the number of non-null values in a column. Suppose you want to find out how many products are sold in your store; you can use the following query: SELECT COUNT(*) as total_products FROM products; 2.SUM() The SUM() function returns the total of a numerical column. It is typically used when you need to find the total of values such as sales income, quantities, or expenses. Imagine that you want to know your company's entire revenue from sales; you can do so by running the following query: SELECT SUM(sales_amount) as total_revenue FROM sales_data; 3.AVG() When you need to calculate the average (mean) value of a numeric column, the AVG() function is your go-to. It is useful when looking for the average price, rating, units sold, and so on. Here is an example in a query: SELECT AVG(Price) FROM Products; 4.MIN() and MAX() The MIN() function returns the smallest value within a column. It's especially useful for locating the lowest or smallest value in a dataset. On the other hand, the MAX() function returns the largest value within a column. It is useful for determining the highest value in a dataset. An example respectively; SELECT MIN(price) FROM pieces; SELECT MAX(price) FROM pieces; Other functions include; Let's break down how ORDER BY, GROUP BY, HAVING, and LIMIT work in SQL with a concise example. Example; Suppose you have a table named sales with the following columns: • id (integer) • product (varchar) • quantity (integer) • price (decimal) • sale_date (date) SQL Query Explanation GROUP BY: This clause groups rows that have the same values in specified columns into summary rows. HAVING: This clause filters groups based on a condition, similar to WHERE but for groups. ORDER BY: This clause sorts the result set by specified column(s). LIMIT: This clause restricts the number of rows returned. Example Query SELECT product, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_sales FROM sales GROUP BY product HAVING SUM(quantity) > 100 ORDER BY total_sales DESC LIMIT 10; Explanation of the Query SELECT: Choose the columns to display. Here, we select product, the sum of quantity as total_quantity, and the sum of price * quantity as total_sales. FROM: Specify the table to query, which is sales. GROUP BY: Group the results by product. HAVING: Filter groups where the total quantity is greater than 100. ORDER BY: Sort the results by total_sales in descending order. LIMIT: Return only the top 10 rows. This query will give you the top 10 products with the highest total sales, but only for those products where the total quantity sold is greater than 100. Feel free to adapt these examples to fit your specific needs! Remember practice makes perfect Good Luck!

SELECT COUNT(*) as total_products
FROM products;

SELECT SUM(sales_amount) as total_revenue
FROM sales_data;

SELECT AVG(Price)
FROM Products;

SELECT MIN(price) 
FROM pieces;

SELECT MAX(price)
FROM pieces;

SELECT 
    product, 
    SUM(quantity) AS total_quantity, 
    SUM(price * quantity) AS total_sales
FROM 
    sales
GROUP BY 
    product
HAVING 
    SUM(quantity) > 100
ORDER BY 
    total_sales DESC
LIMIT 
    10;