troubleshooting Question

Problem with dublicates and SUM Sql Query

Avatar of Madsing
MadsingFlag for Denmark asked on
Microsoft SQL Server 2008
9 Comments1 Solution235 ViewsLast Modified:
I have 2 tables:

Customer table:
customer(nvarchar(255))

Certificates
customer(nvarchar(255))
cert(int)


Customer

570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266
570715000000038266

Certificate
    Customer                     cert
570715000000038266      1383
570715000000038266      1656
570715000000038266      1944
570715000000038266      -1383
570715000000038266      -1350
570715000000038266      1350
570715000000038266      -1944
570715000000038266      850
570715000000038266      -1532
570715000000038266      1532
570715000000038266      774
570715000000038266      -1656
570715000000038266      -774


When I execute this query:

SELECT     [Customer].[Customer], SUM(Certificate.Cert)
FROM         [Customer]
inner JOIN Certificate ON [Customer].[Customer] = Certificate.[Customer]
WHERE [Customer].[Customer] = '570715000000038266'
GROUP BY [Customer].[Customer]

I get this result:
 Customer                           Cert
570715000000038266    12750

It multiplies the customer records with all the certificates lines.
15 customers records X all the matching cert records = 12750

The result i am looking for is:

 Customer                           Cert
570715000000038266    850    (12750/15=850)

There are more than one customer in the customer table with multiple lines…


Hope you can help!?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros