SQL : Creating View using MAX() + SUM() with 3 related tables

Question

In SQL Server, I have a view contains 3 related tables. (Material , Order, OrderDetail)

Each "Order" contains several "OrderDetail"，and each "OrderDetail" contain 1 "Material".

Table Material
----------------------------
MaterialID      MaterialName
    1               egg
    2               flour
    3               butter

Table Order
----------------------------
OrderID               Date
    1              2019-1-01
    2             2019-12-12
    3              2019-6-06

Table OrderDetail
----------------------------
DetailID         OrderID       MaterialID     Price     Quantity    Total
    1                1             1             10       5           50 
    2                1             2            100       5          500 

    3                2             2            200       4          800    
    4                2             3           2000       4         8000

    5                3             3           1000       3         3000
    6                3             1             20       3           60

This is the original view i created

View Original 
----------------------------
MaterialID         OrderID         Date       Price        Total
    1                1         2019-1-01         10           50 
    1                3         2019-6-06         20           60 

    2                1         2019-1-01        100          500    
    2                2        2019-12-12        200          800

    3                2        2019-12-12       2000         8000
    3                3         2019-6-06       1000         3000

Now what I want it to find "Latest Price" (which bind with "Latest Date") and "Sum" (overall money I spend on EACH material)

View Edit 
----------------------------
MaterialID       OrderID     LatestDate      LatestPrice      Sum           
    1              3         2019-6-06           20           110

    2              2        2019-12-12          200          1300 

    3              2        2019-12-12         2000         11100

I know how to filter each column individually with Max(Date) Max(Price) Sum(total) Group By, but I am still unfamiliar with Subquery, INNER join, and other techniques.

Thank you for your time. I've tried to illustrate the problem the best I can, if any additional info is needed please ask anytime :).

Answer 1

This answers your original question using CROSS APPLY:

SELECT m.MaterialID, od.OrderID, od.Date, od.Price, od.Total
FROM Material m CROSS APPLY JOIN
     (SELECT TOP (1) od.*, o.date
      FROM OrderDetail od INNER JOIN
           Orders o
           ON od.OrderID = o.OrderID
      WHERE m.MaterialID = od.MaterialID
      ORDER BY o.Date DESC
     ) od
ORDER BY m.MaterialID;

This answers your revised question using window functions:

SELECT MaterialID, oOrderID, Date, Price, Total, sum_total
FROM (SELECT m.MaterialID, od.OrderID, o.Date, 
             od.Price, od.Total,
             SUM(od.total) OVER (PARTITION BY m.MaterialId) as sum_total,
             ROW_NUMBER() OVER (PARTITION BY m.MaterialId ORDER BY o.Date DESC) as seqnum
      FROM Material m INNER JOIN
           OrderDetail od
           ON m.MaterialID = od.MaterialID INNER JOIN
           Orders o
           ON od.OrderID = o.OrderID
     ) mod
WHERE seqnum = 1
ORDER BY m.MaterialID;

Answer 2

You can do it with a CTE:

WITH CTE AS (
  SELECT 
    m.MaterialID, od.OrderID, o.Date, 
    od.Price, od.Total
  FROM Material m 
  INNER JOIN OrderDetail od ON m.MaterialID = od.MaterialID 
  INNER JOIN [Order] o ON od.OrderID = o.OrderID
)

SELECT 
    c.MaterialID, c.OrderID, c.Date LatestDate, 
    c.Price LatestPrice, g.Sum
FROM CTE c INNER JOIN (
  SELECT MaterialID, MAX(Date) maxdate, SUM(Total) Sum
  FROM CTE
  GROUP BY MaterialID
) g ON g.MaterialID = c.MaterialID AND g.maxdate = c.Date 
ORDER BY c.MaterialID

See the demo.
Results:

> MaterialID | OrderID | LatestDate          | LatestPrice |  Sum
> ---------: | ------: | :------------------ | ----------: | ---:
>          1 |       3 | 06/06/2019 00:00:00 |          20 |  110
>          2 |       2 | 12/12/2019 00:00:00 |         200 | 1300
>          3 |       2 | 12/12/2019 00:00:00 |        2000 |11000