0

I currently have the following query that i wish to execute

SELECT Buyer.nCustomerID, Order.[Order Number], SUM(Order.[Order Total Cost])
FROM [Order] INNER JOIN [Buyer] ON Order.nCustomerID = Buyer.nCustomerID
GROUP BY Buyer.nCustomerID,Order.[Order Number];

However My nCustomerID could be duplicated so i wish to do the following: if nCustomerID appears more then once, add the Order Total Cost together. I beleive that my problem is with Group By.

Group By should be set to just Buyer.nCustomerID however if i remove the Order.[Order Number] i Get the following error:

[ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'Order Number' as part of an aggregate function.

If i leave the Order.[Order Number] in the query then it keeps the duplicate field.

Can anyone offer any advice with this issue?

Liam Sorsby
  • 2,912
  • 3
  • 28
  • 51
  • Why do you have Order Number in the query? If you only have nCustomerID in both SELECT and GROUP BY there will not be a problem. – user1917229 Jan 21 '14 at 10:41
  • @user1917229 I have explained that i get an error message which is above if i remove Order Number – Liam Sorsby Jan 21 '14 at 10:44

1 Answers1

1

I think that you mean:

SELECT Buyer.nCustomerID, SUM(Order.[Order Total Cost])
FROM [Order] INNER JOIN [Buyer] ON Order.nCustomerID = Buyer.nCustomerID
GROUP BY Buyer.nCustomerID

It is possible that you do not need two tables.

user1917229
  • 170
  • 1
  • 13
  • I do need two tables as this is an ecommerse solution that already has the database structure predefined. I am just creating a java plugin to modify the data but i need to select the data first – Liam Sorsby Jan 21 '14 at 10:49