CODEWITHSUNDEEP
sql-serverjoinduplicatessum
jlarkins
jlarkins

Reputation: 89

Join when table A has duplicate records

My head is about to explode with everything I've read about SQL Server and Joins in the last 2 hours.

tbl_customers
-------------
IPaddress
CustomerID


tbl_purchases
-------------
OrderID (pkey)
CustomerID
OrderTotal

I want to get the total purchase amounts per IP address. There are more columns in the tbl_customers table, such that there are duplicate (IPaddress, CustomerID) rows. I have used the following query:

SELECT DISTINCT IPaddress, SUM(OrderTotal) FROM tbl_customers a
INNER JOIN tbl_purchases b ON a.CustomerID = b.CustomerID
GROUP BY IPaddress;

But it retrieves the duplicate rows from tbl_customers and causes the sum function to count the same purchase multiple times. What am I doing wrong? Efficiency isn't really an issue as I'm dealing with under 10K records.

Upvotes: 2

Views: 1944

Answers (3)

Tim Lehner
Tim Lehner

Reputation: 15251

I want to get the total purchase amounts per IP address.

Using your current schema, if a customer makes purchases from more than one IP, then you cannot possibly get such an amount (unless you want to average the amount over all of that customer's IPs or something). Perhaps IPaddress should be in tbl_purchases.

Upvotes: 0

Teja
Teja

Reputation: 13534

SELECT tc.IPaddress,SUM(tp.OrderTotal)
FROM (SELECT DISTINCT IPaddress,CustomerID FROM tbl_customers) tc,
     tbl_purchases tp
WHERE tc.CustomerID = tp.CustomerID
GROUP BY tc.IPaddress;

OR 

SELECT tc.IPaddress,SUM(tp.OrderTotal)
        FROM (SELECT DISTINCT IPaddress,CustomerID FROM tbl_customers) tc
              INNER JOIN 
             tbl_purchases tp
             ON   tc.CustomerID = tp.CustomerID
 GROUP BY tc.IPaddress;

Upvotes: 2

RyanHennig
RyanHennig

Reputation: 1072

First of all, it sounds like your DB is not normalized. If you have a table for customers, and multiple rows with the same CustomerId, does the table have a primary key or is this supposed to be the key to identify a customer?

Upvotes: 0

Related Questions