/***************
* Aggregation *
***************/
-- Find minimum msrp of all products
select min(msrp)
from product;
-- Find maximum msrp of all products
select max(msrp)
from product;
-- Top 2 MSRP of all products;
select *
from product
order by msrp
limit 2;
-- Find average msrp of all products
select avg(msrp) from product;
-- Find how many kind of products we have
select count(product_id)
from product;
-- Find total of msrp of all kind of products
select sum(product_id)
from product;
-- Find total sales revenue(amount);
select sum(price*quantity)
from sales;
select * from sales;
/***************
* Group by *
***************/
-- Find total sales revenue by product. Order by sales_amount desendingly;
select *, price*quantity, sum(price*quantity)
from sales
group by product_id
order by quantity desc;
-- total # of transactions by product
select p.*, count(1) as tran_count
from sales s, product p
where s.product_id = p.product_id
group by s.product_id;
-- count distinct product and client that have sales;
select p.name, count(s.product_id), count(s.client_id)
from sales s,client c,product p
where s.client_id = c.client_id and s.product_id = p.product_id;
-- how many different/unique products each client purchased;
select c.name,count(distinct s.product_id)
from sales s,client c
where s.client_id = c.client_id
group by s.client_id;
-- find clients that purchase more than 1 unique products;
select c.name, count(distinct s.product_id) dis_product
from client c, sales s
where s.client_id = c.client_id
group by s.client_id
having dis_product > 2;
-- practice: find products that are sold less than 20 units;
select p.name, sum(s.quantity) as total_sale
from product p, sales s
where p.product_id = s.product_id
group by s.product_id
having total_sale < 20;