More SQL Syntax

[YoungBuck]

Ok once again here is my database setup (the whole thing this time)

Table = Lists

ListName
ItemName
AmtNum
AmtType

Table = Items

ItemName
Category

Table = Prices

ItemName
AmtType
Price

and here is the SQL statement I am trying to get to work (for test purposes I am not using any variables)

sSQL = "SELECT Lists.ItemName as ItemName, Lists.AmtType as AmtType, Lists.AmtNum as AmtNum, Item.Category as Category, SUM(Lists.AmtNum * Prices.Price) as Total FROM Lists, Items, Prices WHERE ListName = list1 AND Lists.ItemName = Items.ItemName AND Lists.ItemName = Prices.ItemName AND Lists.AmtType = Prices.AmtType ORDER BY Total ASC;"

and I keep on getting Error 3122 - "You tried to execute a query that doesn't include the specified expression 'ItemName' as part of the aggregate function". I didn't receive this error until I started using the SUM function in the SQL statement so I imagine that I am not using it properly. Any explanations of what I need to do/change?

[Paul Warren]

YoungBuck,

This is a real pain in the backside but the only way around it is to put every field into an aggragate function - like the order by clause.

If you've got access to MS Access try building your query in the querybuilder tool, it's useful for this sort of thing. Here's an example of one I did using northwind :

SELECT Customers.CompanyName, Sum([Order Details].UnitPrice) AS SumOfUnitPrice, Orders.OrderDate
FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
GROUP BY Customers.CompanyName, [Order Details].OrderID, Orders.OrderDate;

Ignore the fact that it's got joins in it, the point is for me to list the customer name and order date I had to include them in the ORDER BY clause.

If you think about it it makes sense because your telling the db to aggregate one column ( SUM it ) but not the others which is like looking at the table in two different ways. It thinks you're asking it for a grand total of all the items plus each individual item in the table, which logically speaking just doesn't work. By telling it to group the records you're telling it you want group totals. I hope I've explained this clear enough but if you want some more help please post back here.

[YoungBuck]

Thanks Paul I figured it out here is the result...

sSQL = "SELECT Lists.ItemName as ItemName, Lists.AmtType as AmtType, Lists.AmtNum as AmtNum, Items.Category as Category, (Prices.Price * Lists.AmtNum) as ItemTotal FROM Lists, Items, Prices WHERE ListName = '" & pListName & "' AND Lists.ItemName = Items.ItemName And (Lists.ItemName = Prices.ItemName And Lists.AmtType = Prices.AmtType) ORDER BY " & pSortBy & " ASC;"

Man that is long as hell when you see it with line breaks, lol.