aggregation_functions.sql

-- Count the number of customer records present in the database
SELECT COUNT(customer_id)
FROM customers;

-- Find the average price of our products?
SELECT AVG(price) AS Avg_Price
FROM products;

SELECT SUM(price) / COUNT(price) -- Here avg is sum of price divided by count of price
FROM products;

SELECT SUM(price) / COUNT(product_id)
FROM products;


-- Find the average price of our products only upto 2 decimal places?
SELECT ROUND(AVG(price),2) AS avg_price
FROM products;

-- Identify the regions where the number of customers is high?
SELECT city, COUNT(customer_id)
FROM customers
GROUP BY city;


-- adding new column status
ALTER TABLE orders
ADD COLUMN status VARCHAR(100);
SELECT * FROM orders;

-- Get the count of different Status
SELECT status, COUNT(order_id) AS number_of_orders
FROM orders
GROUP BY status;

SELECT COALESCE(status, "Not Specified") AS status, COUNT(order_id) AS number_of_orders
FROM orders
GROUP BY status;

-- Group By using 2 Columns
-- Write a SQL query to find the number of payments received for each payment method on 
-- each date in order so as to analyze the trend over span of days

SELECT payment_date, payment_method, COUNT(payment_id) AS number_of_payments
FROM payment
GROUP BY payment_date, payment_method
ORDER BY payment_date, payment_method;

SELECT payment_date, payment_method, COUNT(payment_id) AS number_of_payments
FROM payment
GROUP BY payment_date, payment_method
ORDER BY payment_date ASC, payment_method DESC;

SELECT payment_date, payment_method, SUM(payment_amount) AS total_amount
FROM payment
GROUP BY payment_date, payment_method
ORDER BY total_amount DESC;

-- HAVING CLAUSE
-- Identify the customers who spend more than 5000 on a single order and if single customer 
-- has multiple orders greater than 5000 show the order with the maximum amount. 

SELECT customer_id, MAX(total_amount) AS max_amount
FROM orders
GROUP BY customer_id
HAVING max_amount > 5000;


SELECT customer_id, MAX(total_amount) AS max_amount
FROM orders
GROUP BY customer_id
HAVING max_amount > 5000
ORDER BY max_amount DESC;

-- Questions
-- Group orders by order date and calculate the total order amount for each date

SELECT order_date, SUM(total_amount) AS total_amount
FROM orders 
GROUP BY order_date;

-- Find customers who have placed more than one order

SELECT customer_id, COUNT(order_id) AS total_orders
FROM orders
GROUP BY customer_id
HAVING total_orders > 1;


-- Identify products that have been ordered multiple times in the exact same quantity 
-- across different orders and determine the frequency of these occurrences

SELECT product_id, quantity, COUNT(*) AS COUNT
FROM order_items
GROUP BY product_id, quantity;