How do I compare a string to the output of a subquery in SQL?
There are two tables: OrderID and Order Details.
Orders
OrderID PK
Freight
|....|
Order details:
OrderID PK FK
ProductID PK FK
UnitPrice
Quantity
|....|
Each OrderID
is unique to Orders
, but order information may contain multiple details for the same OrderID
and different ProductId
, UnitPrice
etc.
So, in the order details we can see two, three or more orders ( OrderID
).
My task is to select physical addresses of all freight records that are greater than the total value of the entire order. Freight> UnitPrice * Quantity * (Order ID quantity in order details)
SELECT %%physloc%%
FROM Orders
WHERE Freight > (SELECT SUM(UnitPrice * Quantity)
FROM [Order Details]
GROUP BY OrderID);
And of course I have
'Subquery returned more than 1 value ...'
I'm trying to use Top, but in this case I have the wrong choice.
All I need is to somehow match each load with OrderID records to each record from this subquery with the same OrderID. But I have no idea how to do it.
Maybe someone can find it differently, it would be great.
I am using SQL Server 2008
Thanks everyone.
source to share
My task is to select physical addresses of all freight records that are greater than the total value of the entire order. Freight> UnitPrice * Quantity * (Order ID quantity in order details)
You can achieve this easily by using aliases and filtering the subquery like this:
SELECT %%physloc%%
FROM Orders o
WHERE o.Freight > (SELECT SUM(od.UnitPrice * od.Quantity)
FROM [Order Details] as od
WHERE od.OrderId = o.OrderId);
You don't need a sentence group by
at all because it can split / group the results. By using the aggregate function sum
, you are already returning a scalar value, which can be further filtered using the clausewhere
source to share
The easiest way of thinking in this process:
-
The outer query processes each record in the Orders table. As this happens, the current OrderID is split with the subquery. This is made possible by the "where" subquery. This ensures that the data summarized in the subquery has the same OrderID.
-
Using aliases makes referencing tables much easier than specifying the full name of each table and the corresponding column. Format: tableName.Column eg. Orders.OrderID. The alternative alias is much simpler. o.OrderID, where "o" is defined as an order alias.
SELECT %%physloc%%
FROM Orders o
WHERE Freight > (SELECT SUM(UnitPrice * Quantity)
FROM [Order Details] od
WHERE od.OrderID = o.OrderID
GROUP BY OrderID);
source to share
You should just join the request you want to use as such.
SELECT
*
FROM Orders as o
INNER JOIN (SELECT od.OrderId, SUM(od.UnitPrice * od.Quantity) as Total
FROM Details as od
GROUP BY od.OrderId) as d ON d.orderId = o.Id
WHERE o.Freight > d.Total;
http://sqlfiddle.com/#!6/a151f/6
edit:
Also note that you have OrderId as the primary key in the Orders table, which is usually an annotation for foreign keys.
source to share