6-SQL-Aggregation_Function

Aggregation Functions in SQL

Aggregation functions are used to summarize data from multiple rows into a single value.

They are very important for:

📊 Data analysis
📈 Dashboards
📉 Business reporting
💼 Data analyst interviews

1️⃣ COUNT() : Counts Rows
The count() function counts the number of rows that match a specified condition.

2️⃣ SUM() : Adds Values
The sum() function calculates the total sum of a numeric column.

3️⃣ AVG() : Average Value
The avg() function calculates the average value of a numeric column.

4️⃣ MIN() : Smallest Value
The min() function returns the smallest value in a specified column.
 
5️⃣ MAX() : Largest Value
The max() function returns the largest value in a specified column.

NOTE: ROUND() function is used to round a numeric value to a specified number of decimal places. It is not an aggreaction fuction
but is often used with aggregation functions to format the output.

EXAMPLE:

USE market_star_schema;

SELECT
COUNT(market_fact_id) AS no_of_sales,
SUM(sales) AS total_sales,
AVG(sales) AS avg_sales,
MAX(sales) AS max_sales,
MIN(sales) AS min_sales
FROM market_fact_full;

-- how many null values are present in the manu_id column
SELECT COUNT(*) - COUNT(manu_id) -- this line counts the total number of rows & subtracts the count of non-null manu_id values.
FROM prod_dimen;

-- no of customers from city of Cochin
SELECT COUNT(cust_id) FROM cust_dimen
WHERE city='Cochin';

-- no of orders are placed in year of 2010
SELECT COUNT(ord_id) AS no_of_orders FROM orders_dimen
WHERE order_date BETWEEN '2010-01-01' AND '2010-12-31';

-- no of orders are placed in year of March 2010 with critical as order priority
SELECT COUNT(ord_id) AS no_of_orders FROM orders_dimen
WHERE (order_date BETWEEN '2010-03-01' AND '2010-03-31')
AND order_priority='Critical';

-- no of customers either from city of Chennai or Hyderabad
SELECT COUNT(cust_id) AS no_of_customers FROM cust_dimen
WHERE city IN ('Chennai','Hyderabad');

-- find the city with highest no of customers
SELECT city, COUNT(*) AS no_of_cust 
FROM cust_dimen
GROUP BY city
ORDER BY no_of_cust DESC
LIMIT 1;

-- find third highest customer segment in cities of Chennai or Delhi with more no of customers
SELECT customer_segment, COUNT(*) AS no_of_customers 
FROM cust_dimen
WHERE city IN ('Chennai','Delhi')
GROUP BY customer_segment
ORDER BY no_of_customers DESC
LIMIT 1 OFFSET 2;

-- find the sum of sales 
SELECT ROUND(SUM(sales),2) AS total_sales
FROM market_fact_full;

-- find the maximum profit value among the orders which has sales value more than 1000
SELECT ROUND(MAX(profit),2) AS max_profit
FROM market_fact_full
WHERE sales>1000;

-- find the minimum sales value among the orders which has profit value more than 500
SELECT ROUND(MIN(sales),2) AS min_sales
FROM market_fact_full
WHERE profit>500;

-- find the average profit value among the orders which has sales value more than 500 and profit value less thn 1000
SELECT ROUND(AVG(profit),2) AS avg_profit
FROM market_fact_full
WHERE sales>500 AND profit<1000;