Not sure how code this SQL statement?

[blakemckenna]

The following statement doesn't return anything even though it executes successfully. I really don't even want to use all the variables but the one that I need is the @ordTotal because I'm using that in the HAVING clause. How do I code this to where I can have the check in the HAVING clause?

SELECT
   @custID = Customers.CustomerID
  ,@company = Customers.CompanyName
  ,@orderID = Orders.OrderID
  ,@ordDate = Format(Orders.OrderDate, 'd','us')
  ,@prodID = [Order Details].ProductID
  ,@prodName = Products.ProductName
  ,@unitPrice = [Order Details].UnitPrice
  ,@qty = [Order Details].Quantity
  ,@discount = [Order Details].Discount
  ,@ordTotal = ([Order Details].UnitPrice * [Order Details].Quantity)
FROM
  Orders
  INNER JOIN Customers
    ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN [Order Details]
    ON Orders.OrderID = [Order Details].OrderID
  INNER JOIN Products
    ON [Order Details].ProductID = Products.ProductID
GROUP BY Customers.CustomerID
  ,Customers.CompanyName
  ,Orders.OrderID
  ,Orders.OrderDate
  ,[Order Details].ProductID
  ,Products.ProductName
  ,[Order Details].UnitPrice
  ,[Order Details].Quantity
  ,[Order Details].Discount
HAVING SUM(@ordTotal) > 15000;

Thanks!

[Zvoni]

Let's bring it into something more readable. No need for the Variables

SELECT 
    C.CustomerID, C.CompanyName, O.OrderID, Format(O.OrderDate, 'd', 'us) As OrdDate,
    OD.ProductID, P.ProductName, OD.UnitPrice, OD.Quantity, OD.Discount, OD.UnitPrice*OD.Quantity AS OrdTotal 
FROM Orders AS O 
INNER JOIN Customers AS CU ON CU.CustomerID=O.CustomerID 
INNER JOIN [Order Details] AS OD ON OD.OrderID=O.OrderID
INNER JOIN Products AS P ON P.ProductID=OD.ProductID 
WHERE OD.UnitPrice*OD.Quantity>15000;

First off: You're grouping, but you don't have a single Aggregation done in your Select.
Your HAVIN SUM(OrdTotal) won't work, since you do Group by all fields, meaning you get a single line for each ProductID, OrderID, CustomerID and so on.
meaning you will never get a total SUM of a Customer (or an Order, or whatever).

The Query above (resp. your "original") will return all LINE-Items wich have an Order-"Worth" greater 15000
I doubt that is your intention

What exactly are you trying to achieve?

[techgnome]

Also, you're selecting what could be potentially multiple rows, but then assigning them to parameters... which means you'll only get one result, and it'll be the last one it finds. Additionally, it won't return anything. What it does is filling the parameters for you. So if you run it and expect to see a resultset of value in the output window, you'll be disappointed. You have to either print, or SELECT the variables to see what's in them. But zivoni is right, it isn't clear what you're trying to do exactly.


-tg

[Zvoni]

But zivoni is right, it isn't clear what you're trying to do exactly.
-tg

At a guess:
1) "Show me all customers with those orders, which exceeds 15K USD per Total "Worth" of an order"
2) "Show me all customers with a Turnover greater than 15K USD"
3) "Show me all Customers and their orders, where a Line-Item exceeds 15K USD" (That's what my query above is doing)

EDIT: tg is right. Didn't think of that, since i use variables in SQL-Statements once in 20 years:
A variable can only hold one value, in your case, as tg pointed out, the last Recordset