Problem with Join and sub-consumption

Question

I have an exercise to answer, however I can not get the complete solution, only partial ideas, the DBMS used is SQL Server.

Considering the Customers, Orders and Order Details tables create an SQL query that brings the order ID, the order date, the name of the company that placed the order and the total order value.

/****** [Customers]    ******/
CREATE TABLE [dbo].[Customers](
    [CustomerID] [nchar](5) NOT NULL,
    [CompanyName] [nvarchar](40) NOT NULL,
    [ContactName] [nvarchar](30) NULL,
    [ContactTitle] [nvarchar](30) NULL,
    [Address] [nvarchar](60) NULL,
    [City] [nvarchar](15) NULL,
    [Region] [nvarchar](15) NULL,
    [PostalCode] [nvarchar](10) NULL,
    [Country] [nvarchar](15) NULL,
    [Phone] [nvarchar](24) NULL,
    [Fax] [nvarchar](24) NULL
);

/****** [Orders]    ******/
CREATE TABLE [dbo].[Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [nchar](5) NULL,
    [EmployeeID] [int] NULL,
    [OrderDate] [datetime] NULL,
    [RequiredDate] [datetime] NULL,
    [ShippedDate] [datetime] NULL,
    [ShipVia] [int] NULL,
    [Freight] [money] NULL,
    [ShipName] [nvarchar](40) NULL,
    [ShipAddress] [nvarchar](60) NULL,
    [ShipCity] [nvarchar](15) NULL,
    [ShipRegion] [nvarchar](15) NULL,
    [ShipPostalCode] [nvarchar](10) NULL,
    [ShipCountry] [nvarchar](15) NULL
);

/****** [Orders Details]    ******/
CREATE TABLE [dbo].[Order Details](
    [OrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL 
);

What’s wrong with that code, I can’t find the solution:

SELECT Orders.OrderID, OrderDate, CompanyName, 
       (SELECT OrderID,   SUM([UnitPrice]) 
         FROM [Order Details] 
         GROUP BY OrderID 
         HAVING COUNT(OrderID)=1) AS TOTAL
 FROM Orders
     INNER JOIN Customers ON Customers.CustomerID = Orders.CustomerID
     INNER JOIN [Order Details] ON [Order Details].OrderID = Orders.OrderID;

Answer 1

There are some ways to get the result. Considering the code you posted, it seems to me that just review the calculation of the request totalization.

The subconsultation

  SELECT OrderID,   SUM([UnitPrice]) 
     FROM [Order Details] 
     GROUP BY OrderID 
     HAVING COUNT(OrderID)=1

shall be transformed into a correlated sub-groove

  SELECT sum (OD.UnitPrice) 
     from [Order Details] as OD
     where OD.OrderID = Orders.OrderID

The whole code would look like this:

SELECT Orders.OrderID, Orders.OrderDate, Customers.CompanyName, 
      (SELECT sum (OD.UnitPrice) 
         from [Order Details] as OD
         where OD.OrderID = Orders.OrderID) as TOTAL
 from Orders
     inner join Customers on Customers.CustomerID = Orders.CustomerID;