סכימת הזמנות

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