help with JOIN please

[andrewbadera]

the following statement:

SELECT DISTINCT(tOrder.intOrderID), SUM(CAST(ProductOrder.monTotal AS money)) As OrderTotal, ProductOrder.intProductID, tOrder.dtReceived, tOrder.dtShipped, Product.intProductID, tUser.intUserID FROM tblProductOrder As ProductOrder JOIN tblProduct AS Product ON Product.intProductID = ProductOrder.intProductID JOIN tblOrder AS tOrder ON tOrder.intOrderID = ProductOrder.intOrderID JOIN tblUser AS tUser ON tOrder.intUserID = tUser.intUserID WHERE tOrder.isActive = 1 GROUP BY tOrder.intOrderID, tOrder.dtReceived, tUser.intUserID, ProductOrder.intProductID, tOrder.dtShipped, Product.intProductID ORDER BY tOrder.intOrderID ASC, tOrder.dtShipped ASC

is returning to me a record for each item (ProductOrder.intProductOrderID) in each order rather than the list of individual orders (tOrder.intOrderID) with order totals (consisting of the SUM of OrderProduct.monTotal for all OrderProducts by intOrderID), as I had intended.

can anyone help me clean this up?

 

[tlbroadbent]

SQL is doing exactly what you instructed it to do. The Group By clause contains 6 columns. This instructs SQL to aggregate data for the 6 columns. The Distinct clause is useless in this query. If you want to show totals by intOrderID, you must group by that column alone.

SELECT
tOrder.intOrderID,
CAST(Sum(ProductOrder.monTotal) AS money) As OrderTotal
FROM tblProductOrder As ProductOrder
JOIN tblOrder AS tOrder
ON tOrder.intOrderID = ProductOrder.intOrderID
WHERE tOrder.isActive = 1
GROUP BY
tOrder.intOrderID,
ORDER BY
tOrder.intOrderID

If other columns (such as DtReceived and DtShipped) are unique per orderID you can add them to the select list and group by clause. Otherwise, you will have to use aggregate functions (MIN, MAX) to select the values you want in the result set. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[andrewbadera]

Problem being:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Column 'tOrder.dtReceived' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

/report_orders.asp, line 47

Anything in my SELECT statement that I don't aggregate or GROUP pops an ODBC error ...

 

[andrewbadera]

the product data shouldn't have been pulled back in that statement, just the product-order data. also, I eliminated the user id, the viewer will simply have to view the individual order report. problem solved.

 

[tlbroadbent]

"Anything in my SELECT statement that I don't aggregate or GROUP pops an ODBC error ..."

That is correct. That is why I said, "you can add them to the select list and group by clause. Otherwise, you will have to use aggregate functions (MIN, MAX) to select the values you want in the result set." Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[andrewbadera]

sorry, how do you mean, group by clause, when pulling back dtReceived and dtShipped, which, yes, are unique by order ID ...

 

[tlbroadbent]

GROUP BY
tOrder.intOrderID,
tOrder.dtReceived,
tOrder.dtShipped
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[andrewbadera]

oh, ok, so nothing different than what I ended up doing, which was removing the unecessary user and irrelevant product references from the entire query ...