I am trying to select the Top 5 best-selling products in terms of quantity per year.
CREATE TABLE purchaseorderheader(
purchaseorderid NUMBER(4),
revisionnumber NUMBER(2),
status NUMBER(1),
employeeid NUMBER(3),
vendorid NUMBER(4),
shipmethodid NUMBER(1),
orderdate TIMESTAMP,
shipdate TIMESTAMP,
subtotal FLOAT(10),
taxamt FLOAT(10),
freight FLOAT(10),
modifieddate TIMESTAMP,
PRIMARY KEY(purchaseorderid)
);
CREATE TABLE purchaseorderdetail(
purchaseorderid NUMBER(4),
purchaseorderdetailid NUMBER(4),
duedate TIMESTAMP,
orderqty NUMBER(6),
productid NUMBER(6),
unitprice FLOAT(10),
receivedqty FLOAT(10),
rejectedqty FLOAT(10),
modifieddate TIMESTAMP,
PRIMARY KEY(purchaseorderdetailid),
CONSTRAINT fk_orderid FOREIGN KEY (purchaseorderid) REFERENCES purchaseorderheader(purchaseorderid)
);
Here's what I've done so far but it's not by year and I have no clue of how to do it...
SELECT EXTRACT(year FROM h.orderdate) "Year", d.productid, SUM(d.orderqty) "Somme"
FROM purchaseorderheader h
INNER JOIN purchaseorderdetail d ON h.purchaseorderid = d.purchaseorderid
GROUP BY EXTRACT(year FROM h.orderdate), d.productid
ORDER BY "Somme"
DESC FETCH FIRST 5 ROWS ONLY;
I also would like to select The Top 5 vendors who have the lowest percentage of rejected received items if you have any idea
