Link multiple table with math operation in SQL

Question

I'm trying to find the revenue from customers in an country (United States). The relevant tables are: Order Details: Order ID, Unit price, quantity, Orders: Order ID, customerID Customers: customerID, country.

I'm not sure how to do this. I was thinking multiple inner join but it doesn't work. The error message is "Syntax error (missing operator) in query expession 'ORDER DETAILS].ORDERID = ORDER.ORDERID INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID' MS online said Error 3075

Here is what I have:

SELECT SUM(QUANTITY*UNITPRICE) AS Result
FROM [ORDER DETAILS]
INNER JOIN ORDERS ON [ORDER DETAILS].ORDERID = ORDER.ORDERID
INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID
WHERE COUNTRY = 'Argentina'

Thanks in advance.

Edit: table structure http://postimg.org/image/oojygytkv/

"Doesn't work" doesn't work for anyone. Explain your actual problem - is there an error or are you getting too many rows, not enough rows, incorrect results. — Nick.McDermaid

Linger Linger · Accepted Answer · 2013-11-28T04:22:50

In Access if you are JOINing more than two tables together then it requires parenthesis. Try the following:

SELECT SUM(QUANTITY * UNITPRICE) AS Result
FROM ([ORDER DETAILS]
INNER JOIN ORDERS ON [ORDER DETAILS].ORDERID = ORDERS.ORDERID)
INNER JOIN CUSTOMERS ON CUSTOMERS.CUSTOMERID = ORDERS.CUSTOMERID
WHERE COUNTRY = 'Argentina'

Link multiple table with math operation in SQL

1 Answers