Inner join multiple SQL table and do calculations

Question

I need to calculate in September 1996 what proportion of each product contribute to total revenue. The data are in 3 tables

Table #1: OrderDetails

OrderDetailID   OrderID ProductID   Quantity-
-------------------------------------------
1               10248       11      12
2               10248       42      10
3               10248       72       5
4               10249       14       9
5               10249       51      40
6               10250       41      10

Table #2: Products

ProductID   ProductName                     SupplierID  CategoryID       Unit           Price
-----------------------------------------------------------------------------------------------------
    1       Chais                               1           1       10  boxes x 20 bags 18
    2       Chang                               1           1       24 - 12 oz bottles  19
    3       Aniseed Syrup                       1           2       12 - 550 ml bottles 10
    4       Chef Anton's Cajun Seasoning        2           2       48 - 6 oz jars      22
    5       Chef Anton's Gumbo Mix              2           2       36 boxes            21.35
    6       Grandma's Boysenberry Spread        3           2       12 - 8 oz jars      25
    7       Uncle Bob's Organic Dried Pears     3           7       12 - 1 lb pkgs.     30

Table #3: Orders

OrderID CustomerID  EmployeeID  OrderDate   ShipperID
------------------------------------------------------
10248   90          5           1996-07-04  3
10249   81          6           1996-07-05  1
10250   34          4           1996-07-08  2
10251   84          3           1996-07-08  1
10252   76          4           1996-07-09  2
10253   34          3           1996-07-10  2
10254   14          5           1996-07-11  2

I guess the steps are:

1. inner join OrderDetails with Order by OrderID to only show Orders in Sep 1996
2. inner join the result in step 1 with Products by ProductID and calculate each product's revenue percentage over total revenue

I've done step one

SELECT
    Orders.OrderID,
    Orders.OrderDate,
    OrderDetails.Quantity,
    OrderDetails.ProductID
FROM
    Orders
INNER JOIN
    OrderDetails ON  Orders.OrderID = OrderDetails.OrderID
WHERE
    OrderDate LIKE '1996-09%';

The expected result:

OrderID OrderDate   Quantity    ProductID
-----------------------------------------
10295   1996-09-02   4          56
10296   1996-09-03  12          11
10296   1996-09-03  30          16
10296   1996-09-03  15          69
10297   1996-09-04  60          39

But I don't know how to do step 2. Any suggestions please ? thank you very much!

Answer 1

Use window functions:

SELECT od.ProductID,
       SUM(od.Quantity),
       SUM(od.Quantity) * 1.0 / SUM(SUM(od.Quantity)) OVER () as ratio
FROM Orders o INNER JOIN
     OrderDetails od
     ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '1996-09-01' AND o.OrderDate < '1996-10-01'
GROUP BY od.ProductID;

Answer 2

It looks like W3Cschool's interactive SQL practice doesn’t support window functions. Moreover, I didn’t find a way to define a variable, so had to mention the same constant (year and month) twice. In real life I would suggest using of analytic functions or at least declare a variable for a value used in several places.

Anyway, it looks like the following code doing what you need:

SELECT 
  od.ProductID, 
  MIN(o.OrderDate) as SalesStart, 
  MAX(o.OrderDate) as SalesEnd, 
  SUM(od.Quantity) as SoldQty, 
  SUM(od.Quantity * p.Price) as SoldAmt, 
  SUM(od.Quantity * p.Price) * 1.0 / 
  (
    SELECT SUM(odAll.Quantity * pAll.Price) 
    FROM OrderDetails odAll 
    INNER JOIN Orders as oAll 
      ON oAll.OrderID = odAll.OrderID
    INNER JOIN Products as pAll
      ON odAll.ProductID =  pAll.ProductID
    WHERE oAll.OrderDate LIKE '1996-09%') as PortionInTotalSales
FROM Orders as o
INNER JOIN OrderDetails as od
  ON o.OrderID = od.OrderID
INNER JOIN Products as p
  ON od.ProductID =  p.ProductID  
WHERE o.OrderDate LIKE '1996-09%'
GROUP BY od.ProductID