כתבו שאילתה אשר תסכום לכל יום ולכל סוג לקוח את כמות ההזמנות אשר נשלחו בגבולות המדינה וכמות ההזמנות אשר נשלחו מעבר לגבולות המדינה
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