Total sum fields from joined tables

Question

Request table for order:

SELECT O.ID, C.SecondName, E.SecondName, O.DateOf , O.ClientID, O.EmployeeID, O.Desc
FROM ((Client AS C INNER JOIN [ORDER] AS O ON C.ID = O.ClientID) 
    INNER JOIN Employee AS E ON E.ID = O.EmployeeID) 

Query table for orders:

SELECT  OrderItem.ID, 
        ProductID, 
        OrderID, 
        Quantity, 
        P.Title,
        P.CurrentPrice, 
        P.ID, 
        (P.CurrentPrice* OrderItem.Quantity) AS Total
FROM 
    OrderItem 
INNER JOIN 
    Product AS P 
ON 
    OrderItem.ProductID = P.ID
GROUP BY 
    OrderID,
    OrderItem.ID, 
    ProductID, 
    Quantity, 
    P.Title,
    P.CurrentPrice, 
    P.ID

and for the total price of the order:

select OrderID, sum(Total)
from (
SELECT 
    OrderItem.ID
    , ProductID
    , OrderID
    , Quantity
    , P.Title
    ,P.CurrentPrice
    , P.ID
    , (P.CurrentPrice* OrderItem.Quantity) AS Total
FROM OrderItem 
INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
) t 

group by OrderId

How can I combine tables to get the full order price field for the order table?

Answer 1

1) The third request, which you call the "total order price", has an unnecessary subquery and fields. Here's the equivalent:

SELECT 
    OrderID, 
    Sum(P.CurrentPrice* OrderItem.Quantity) AS OrderTotal
FROM OrderItem INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
GROUP BY OrderId

2) " ", , . (?) , , []... , , / .

:

SELECT  
    O.ID, O.DateOf , O.ClientID, O.EmployeeID, O.Desc,
    Sum(P.CurrentPrice* OrderItem.Quantity) AS [OrderTotal]
FROM
    ([Order] As O INNER JOIN OrderItem ON O.ID = OrderItem.OrderID)
    INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
GROUP BY 
    O.ID, O.DateOf , O.ClientID, O.EmployeeID, O.Desc

, , sum() , Group By.

SELECT  
    O.ID, C.SecondName, E.SecondName, 
    O.DateOf , O.ClientID, O.EmployeeID, O.Desc,
    Sum(P.CurrentPrice* OrderItem.Quantity) AS [OrderTotal]
FROM ((
    (Client AS C INNER JOIN [ORDER] AS O ON C.ID = O.ClientID)
    INNER JOIN Employee AS E ON E.ID = O.EmployeeID)
    INNER JOIN OrderItem ON O.ID = OrderItem.OrderID)
    INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
GROUP BY 
    O.ID, C.SecondName, E.SecondName, O.DateOf,
    O.ClientID, O.EmployeeID, O.Desc

Answer 2

thw sum_total,

( , , orderID)

select 
    t1.ID
  , t1.ProductID
  , t1.OrderID
  , t1.uantity
  , t1.Title
  , t1.CurrentPrice
  , t1.PID
  , t1.Total
  , T2.sum_total
FROM( 
  SELECT  OrderItem.ID, 
          ProductID, 
          OrderID, 
          Quantity, 
          P.Title,
          P.CurrentPrice, 
          P.ID  AS PID, 
          (P.CurrentPrice* OrderItem.Quantity) AS Total
  FROM 
      OrderItem 
  INNER JOIN 
      Product AS P 
  ON 
      OrderItem.ProductID = P.ID
  GROUP BY 
      OrderID,
      OrderItem.ID, 
      ProductID, 
      Quantity, 
      P.Title,
      P.CurrentPrice, 
      P.ID
  ) t1 
INNER JOIN(
    select OrderID, sum(Total) sum_total
    from (
    SELECT 
        OrderItem.ID
        , ProductID
        , OrderID
        , Quantity
        , P.Title
        ,P.CurrentPrice
        , P.ID
        , (P.CurrentPrice* OrderItem.Quantity) AS Total
    FROM OrderItem 
    INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
    ) t 
    group by OrderId
) t2 on t1.OrderID = t2.OrderID