-
March 23rd, 2009, 07:25 PM
#1
SQL Query
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?
Similar Threads
-
By mikester2 in forum Other Tutorials Forum
Replies: 5
Last Post: January 31st, 2005, 01:16 PM
-
By ch4r in forum Other Tutorials Forum
Replies: 5
Last Post: January 18th, 2005, 08:20 AM
-
By SonofGalen in forum The Security Tutorials Forum
Replies: 7
Last Post: February 9th, 2004, 11:14 AM
-
By CXGJarrod in forum Microsoft Security Discussions
Replies: 0
Last Post: July 23rd, 2003, 10:00 PM
-
By s0nIc in forum AntiVirus Discussions
Replies: 2
Last Post: January 27th, 2003, 12:23 PM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|