Wanted: One line per country

[milton747]

Newbie to SQL. SQL works, but what I'm hoping for is one line for all orders for a country. Currently get this:


Australia 1201 22.23
Australia 1233 90.55
Belgium 2344 88.07
Belgium 2348 12.67

Can't think what I need to GROUP BY to get it.
------------------



SELECT
CLASSICMODELS.CUSTOMERS.COUNTRY,
CLASSICMODELS.ORDERS.ORDERNUMBER,
SUM (CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED *
CLASSICMODELS.ORDERDETAILS.PRICEEACH) AS LT


from
CLASSICMODELS.CUSTOMERS,
CLASSICMODELS.ORDERDETAILS,
CLASSICMODELS.ORDERS

WHERE
CLASSICMODELS.CUSTOMERS.CUSTOMERNUMBER =
CLASSICMODELS.ORDERS.CUSTOMERNUMBER

AND
CLASSICMODELS.ORDERS.ORDERNUMBER =
CLASSICMODELS.ORDERDETAILS.ORDERNUMBER

GROUP BY
CLASSICMODELS.CUSTOMERS.COUNTRY,
CLASSICMODELS.ORDERS.ORDERNUMBER,

ORDER BY
CLASSICMODELS.CUSTOMERS.COUNTRY

 

[lameid]

Your SQL does not look like Jet / Access SQL. It does look like SQL Server or pehaps another product with an old ANSI-89 join (in your where clause instead of the from clause).

It should get what you want if you take the ordernumber out of both the select ang group by clauses EXCEPT you are not joining orders and orderdetails which means you are getting a lot of duplicates. You should have something that equals each other like you use customer number in your where clause (most likely an order number).

Next time please try to post in the right forum.

 

[lespaul]

so is this the result you DO want?

[tt]
Australia 1201 22.23 1233 90.55
Belgium 2344 88.07 2348 12.67
[/tt]

Leslie