guitar-shop-database/How_to_code_summary_querries.sql

/*1*/
SELECT
    count(*) AS order_count,
    SUM(tax_amount) AS tax_total
FROM
	orders;

/*2*/
SELECT
	category_name,
	COUNT(product_id) AS product_count,
    MAX(list_price) AS most_expensive_product
FROM 
	products p
JOIN
	categories c
ON
	p.category_id = c.category_id
GROUP BY 
	category_name
ORDER BY COUNT(product_id) DESC, category_name DESC;

/*3*/
SELECT
	email_address,
    SUM(item_price) * quantity AS item_price_total ,
    SUM(discount_amount) * quantity AS discount_amount_total
FROM 
	orders o  
JOIN 
	order_items oi
ON 
	o.order_id = oi.order_id
JOIN 
	customers c
ON 
	o.customer_id = c.customer_id
GROUP BY 
	c.customer_id
ORDER BY 
	SUM(item_price) * quantity DESC;

/*4*/
SELECT
	c.email_address,
	COUNT(oi.order_id) AS 'order_count',
	SUM(item_price - discount_amount) * quantity AS 'order_total'
FROM orders ord JOIN order_items oi
ON o.order_id = oi.order_id
JOIN customers c
ON o.customer_id = c.customer_id
GROUP BY email_address
HAVING COUNT(oi.order_id) > 1
ORDER BY SUM(item_price - discount_amount) DESC, COUNT(oi.order_id);

/*5*/
SELECT
	email_address,
    COUNT(o.order_id) AS 'order_count',
    SUM(item_price - discount_amount) * quantity AS 'order_total'
FROM orders o  
JOIN order_items oi
	ON o.order_id = oi.order_id
    JOIN customers c
    ON o.customer_id = c.customer_id
WHERE oi.item_price > 400
GROUP BY email_address
HAVING COUNT(oi.order_id) > 1
ORDER BY SUM(item_price - discount_amount) * COUNT(oi.order_id)  DESC;


/*6*/ /* kan het dat 1 bedrag niet klopt*/
SELECT
	product_name,
    SUM(item_price - discount_amount) * quantity AS 'Total Amount'
FROM 
	products p 
JOIN 
	order_items o 
ON 
	p.product_id = o.product_id
GROUP BY 
product_name WITH ROLLUP;

/*7*/
SELECT 
	email_address,
    COUNT(product_id) AS 'number_of_products'
FROM 
	customers c 
JOIN 
	orders o
ON 
	c.customer_id = o.customer_id
JOIN 
	order_items ord 
ON 
	o.order_id = ord.order_id
GROUP BY 
	email_address
HAVING 
	COUNT(o.order_id) > 1;