mysql aggregate_mysql: aggregation

/***************

* 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;