Having and Group By

1) Print the description and  total qty sold for each product.

select s.product_no,p.description, sum(s.qty_ordered) from sales_order_details s,product_master p
where p.product_no=s.product_no
group by s.product_no,p.description;

2) Find the value of each product sold.

select  s.product_no,p.description,sum(s.qty_disp*s.product_rate) "Sales Per Product" from

sales_order_details s,product_master p where p.product_no=s.product_no

group by s.product_no,p.description;

3) Calculate the avarage qty sold for each client that has a maximum order value of 15000.00

select c.client_no,c.name,avg(s.qty_disp) "Avg. Sales" from sales_order_details s ,sales_order so,client_master c

where c.client_no=so.client_no and so.s_order_no=s.s_order_no

group by c.client_no,c.name having max(s.qty_ordered*s.product_rate)>15000;

4) find out the total sales amount receivable for the month of jan.it will be the sum total of all the billed orders for the month.

select s.s_order_no,s.s_order_date,sum(so.qty_ordered*so.product_rate)"Order Billed",sum(so.qty_disp*so.product_rate) "Total Amount" from sales_order s, sales_order_details so

 where so.s_order_no=s.s_order_no and s.billed_yn='Y' and to_char(s_order_date,'mon')='jan'

 group by s.s_order_no,s.s_order_date;

5) Print the information of product_master,order_detail table in the following format for all the record:-{description}worth RS{total sales for the products} was sold.

select p.description||' Worth Rs'||sum(d.qty_disp*d.product_rate) from product_master p, sales_order_details d

where p.product_no=d.product_no group by p.description; 

6) Print the information of product_master,order_detail table in the following format for all the records:-{description}worth RS.{total sales for the product} was ordered in the month of (s_order_date in the month format}.

select p.description||' Worth Rs'||sum(d.qty_disp*d.product_rate)||' was ordered in the month of'||to_char(s_order_date,'month')"Description Total amount Month" from product_master p, sales_order_details d,sales_order s

where p.product_no=d.product_no and s.s_order_no=d.s_order_no group by p.description,s.s_order_date;