Databases 29 Oct, sum

northwind.sql

use northwind

select productid, sum(quantity) as totalquan
from [Order Details]
group by productid
having sum(quantity)>1200

select orderid, count(*)
from [Order Details]
group by OrderID
having count(*) > 5

select CustomerID, count(*)  
from Orders
where year(ShippedDate) = 1998
group by CustomerID
having count(*) > 8
order by sum(freight) desc



select productid, orderid, sum(quantity) as total_quantity
from orderhist
group by productid, orderid
with rollup
order by productid, orderid

select null, null, sum(quantity)
from orderhist

select productid, null, sum(quantity)
from orderhist
group by productid

select productid, null, sum(quantity)
from orderhist
group by productid



select productid, orderid, sum(quantity) as total_quantity
from orderhist
group by productid, orderid
with cube
order by productid, orderid

select productid, grouping(productid), 
	orderid, grouping(orderid), 
	sum(quantity) as total_quantity
from orderhist
group by productid, orderid
with cube
order by productid, orderid


-----------------------------------


use joindb

select * from Buyers
select * from Produce
select * from Sales

select buyer_name, sales.buyer_id, qty
from buyers inner join sales
on buyers.buyer_id = sales.buyer_id