I’m having an issue with an SQL query. Below are the tables and fields of importance.

Dbo.ORDERS

OrderID (PK)
Totalcourseamt
Promodiscount
Tierdiscount
Affiliatediscount
Addldiscount
Shippingcharge
Expressdeliverycharge
Totalcharge

Dbo.ORDERDETAILS

Orderdetailskey (PK)
OrderID (FK)
Coursekey (FK)

Dbo.COURSECATALOG

Coursekey (PK)
Coursename

I have the following query:

Code:
SELECT 
c3.coursename, 
c3.statecd, 
SUM(c2.totalcourseamt) AS [Gross],
SUM(c2.promodiscount) AS [PROMO DISCOUNT], 
SUM(c2.tierdiscount) AS [TIER DISCOUNT],
SUM(c2.affiliatediscount) AS [AFFILIATE DISCOUNT],
SUM(c2.addldiscount) AS [ADDL DISCOUNT],
SUM(c2.shippingcharge) AS [SHIPPING],
SUM(c2.expressdeliverycharge) AS [EXPRESS DELIVERY],
SUM(c2.totalcharge) AS [Total_Charge]
FROM orderdetails c1, orders c2, coursecatalog c3
WHERE c1.orderid=c2.orderid
AND c1.coursekey=c3.coursekey
AND c2.orderdate>='20060801' AND c2.orderdate<='20060831'
GROUP BY c1.coursekey, c3.coursename, c3.statecd;
The problem is that if there is more than one entry in the Orderdetails table for a certain order (meaning that one order consists of more than one course), my amounts (TotalCourseAmt, promodiscount, tierdiscount…) get counted more than once. This obviously skews the results (Gross amount is more than twice what it should be, as are all other amounts) – is there any way to prevent this?