|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Subquery Problemusing the below queries based on Northwind so I didn't have to post a DDL. Problem: If you run the code in SUBQUERY PROBLEM below on Northwind, it returns the total freight for each day and the order subtotal that is derived from 2 hard coded dates as seen in SUBQUERY PROBLEM. I need this statement to keep the same structure, but instead of using the hardcoded dates within the subquery, i need it to reference the OrderDate field in the WHERE clause. So the subquery part would look like: WHERE (Orders.OrderDate = OrderDate) The results would sum the freight for each day, then the subtotal of orders. ----- SUBQUERY PROBLEM: SELECT OrderDate, SUM(Freight) AS SumFreight, (SELECT SUM(CONVERT(money, ([Order Details].UnitPrice * [Order Details].Quantity) * (1 - [Order Details].Discount) / 100) * 100) AS Subtotal FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID WHERE (Orders.OrderDate >= '19960704') AND (Orders.OrderDate <= '19960706')) As SumOrders FROM Orders GROUP BY OrderDate Am I missing something or is this all you need?
SELECT OrderDate, SUM(CONVERT(money, ([Order Details].UnitPrice * [Order Details].Quantity) * (1 - [Order Details].Discount) / 100) * 100) AS Subtotal FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = Orders.OrderID GROUP BY OrderDate what i need is for the subquery to use the main query's date within the
subquery where part. i know we could simply re-write it like you did to get correct results. i just used northwind for ease of sample use. i need the sql syntax to stay like i had it. in my example, the subquery select statement will be referencing a different table. Show quote "SQL" <denis.g***@gmail.com> wrote in message news:1137092257.465475.194860@g47g2000cwa.googlegroups.com... > Am I missing something or is this all you need? > > SELECT OrderDate, SUM(CONVERT(money, ([Order Details].UnitPrice * > [Order Details].Quantity) * (1 - [Order Details].Discount) / 100) * > 100) AS > Subtotal > FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = > Orders.OrderID > GROUP BY OrderDate > If it helps you understand why i'm trying to do this, i can't use a join
because the tables only have dates in common and the dates are not related. Show quote "SQL" <denis.g***@gmail.com> wrote in message news:1137092257.465475.194860@g47g2000cwa.googlegroups.com... > Am I missing something or is this all you need? > > SELECT OrderDate, SUM(CONVERT(money, ([Order Details].UnitPrice * > [Order Details].Quantity) * (1 - [Order Details].Discount) / 100) * > 100) AS > Subtotal > FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = > Orders.OrderID > GROUP BY OrderDate > On Thu, 12 Jan 2006 12:41:25 -0600, scott wrote:
Show quote >Although I know there is a better way to achieve the same results, I'm just Hi Scott,>using the below queries based on Northwind so I didn't have to post a DDL. >Problem: If you run the code in SUBQUERY PROBLEM below on Northwind, it >returns the total freight for each day and the order subtotal that is >derived >from 2 hard coded dates as seen in SUBQUERY PROBLEM. > >I need this statement to keep the same structure, but instead of using the >hardcoded dates within the subquery, i need it to reference the OrderDate >field in the WHERE clause. > >So the subquery part would look like: > >WHERE (Orders.OrderDate = OrderDate) > >The results would sum the freight for each day, then the subtotal of orders. > > >----- SUBQUERY PROBLEM: > >SELECT OrderDate, SUM(Freight) AS SumFreight, > (SELECT SUM(CONVERT(money, ([Order Details].UnitPrice * [Order >Details].Quantity) * (1 - [Order Details].Discount) / 100) * 100) AS >Subtotal > FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = >Orders.OrderID > WHERE (Orders.OrderDate >= '19960704') AND (Orders.OrderDate <= >'19960706')) As SumOrders >FROM Orders >GROUP BY OrderDate > I'm not sure what you are attempting and what results you expect. But if the problem is: "How do I reference a column from the Orders table in the outer query, as opposed to the column from Orders in the subquery", then the answer is to use aliases: SELECT a.something, (SELECT b.otherthing FROM TheTable AS b WHERE b.Whatever = a.Whatever) AS subquery_result FROM TheTable AS a -- Hugo Kornelis, SQL Server MVP exactly, thank you.
Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info> wrote in message news:eelds1hphbohc71ti2oa6qgeu0bgrsd9p8@4ax.com... > On Thu, 12 Jan 2006 12:41:25 -0600, scott wrote: > >>Although I know there is a better way to achieve the same results, I'm >>just >>using the below queries based on Northwind so I didn't have to post a DDL. >>Problem: If you run the code in SUBQUERY PROBLEM below on Northwind, it >>returns the total freight for each day and the order subtotal that is >>derived >>from 2 hard coded dates as seen in SUBQUERY PROBLEM. >> >>I need this statement to keep the same structure, but instead of using the >>hardcoded dates within the subquery, i need it to reference the OrderDate >>field in the WHERE clause. >> >>So the subquery part would look like: >> >>WHERE (Orders.OrderDate = OrderDate) >> >>The results would sum the freight for each day, then the subtotal of >>orders. >> >> >>----- SUBQUERY PROBLEM: >> >>SELECT OrderDate, SUM(Freight) AS SumFreight, >> (SELECT SUM(CONVERT(money, ([Order Details].UnitPrice * [Order >>Details].Quantity) * (1 - [Order Details].Discount) / 100) * 100) AS >>Subtotal >> FROM [Order Details] INNER JOIN Orders ON [Order Details].OrderID = >>Orders.OrderID >> WHERE (Orders.OrderDate >= '19960704') AND (Orders.OrderDate <= >>'19960706')) As SumOrders >>FROM Orders >>GROUP BY OrderDate >> > > Hi Scott, > > I'm not sure what you are attempting and what results you expect. But if > the problem is: "How do I reference a column from the Orders table in > the outer query, as opposed to the column from Orders in the subquery", > then the answer is to use aliases: > > SELECT a.something, > (SELECT b.otherthing > FROM TheTable AS b > WHERE b.Whatever = a.Whatever) AS subquery_result > FROM TheTable AS a > > -- > Hugo Kornelis, SQL Server MVP  |
|||||||||||||||||||||||
Other interesting topics