למדידת ביצועי ההזמנות בחברת שילוח בינלאומית, נתונות הטבלאות הבאות –
CREATE TABLE orders (OrderDate date, CustomerTypeID int, ShipTypeId varchar(1), Quantity int)
CREATE TABLE Customers (CustomerTypeID int, CustomerTypeDescription varchar(25))
כתבו שאילתה אשר תסכום לכל יום ולכל סוג לקוח את כמות ההזמנות אשר נשלחו בגבולות המדינה וכמות ההזמנות אשר נשלחו מעבר לגבולות המדינה
CREATE TABLE orders (OrderDate date, CustomerTypeID int, ShipTypeId varchar(1), Quantity int) CREATE TABLE Customers (CustomerTypeID int, CustomerTypeDescription varchar(25)) INSERT INTO orders VALUES(getdate(), 1, 'L' , 20) INSERT INTO orders VALUES(getdate(), 1, 'L' , 15) INSERT INTO orders VALUES(getdate(), 1, 'O' , 22) INSERT INTO orders VALUES(getdate(), 1, 'O' , 38) INSERT INTO orders VALUES(getdate(), 1, 'O' , 72) INSERT INTO orders VALUES(getdate(), 2, 'L' , 54) INSERT INTO orders VALUES(getdate(), 2, 'O' , 12) INSERT INTO orders VALUES(getdate(), 2, 'L' , 80)
-- Solution 1 SELECT S.OrderDate, CT.CustomerTypeID, CT.CustomerTypeDescription , (SELECT SUM(S1.Quantity) FROM Sales S1 WHERE S.OrderDate = S1.OrderDate AND S1.CustomerTypeID = CT.CustomerTypeID AND S1.ShipTypeID = 'O') AS Internationl Shipping, (SELECT SUM(S2.Quantity) FROM Sales S2 WHERE S.OrderDate = S2.OrderDate AND S2.CustomerTypeID = CT.CustomerTypeID AND S2.ShipTypeID = 'L') AS Local Shipping FROM Sales S, CustomerType CT WHERE S.CustomerTypeID = CT.CustomerTypeID GROUP BY S.OrderDate, CT.CustomerTypeID, CT.CustomerTypeDescription -- Solution 2 SELECT S.OrderDate, CT.CustomerTypeID, CT.CustomerTypeDescription , SUM(CASE WHEN S.ShipTypeID = 'O' THEN S.Quantity END) AS Internationl Shipping, SUM(CASE WHEN S.ShipTypeID = 'L' THEN S.Quantity END) AS Local Shipping FROM Sales S, CustomerType CT WHERE S.CustomerTypeID = CT.CustomerTypeID GROUP BY S.OrderDate, CT.CustomerTypeID, CT.CustomerTypeDescription