Problem with dublicates and SUM Sql Query

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!?
MadsingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pivarCommented:
Hi,

Try


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

/peter
0
MadsingAuthor Commented:
Then I get:

Customer                          (Cert)             (AVG cert)
570715000000038266      12750      65,3846153846154

So that is no good.
0
DavidMorrisonCommented:
I think what you need is:

SELECT     [Customer].[Customer],
sum(Certificate.Cert) over (partition by [Customer].[Customer]) /
count(*) over (partition by [Customer].[Customer]) as Cert
FROM         [Customer]
inner JOIN Certificate ON [Customer].[Customer] = Certificate.[Customer]
WHERE [Customer].[Customer] = '570715000000038266'
GROUP BY [Customer].[Customer]
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Start This Course
pivarCommented:
How about


SELECT     [Customer].[Customer], SUM(Certificates.Cert),
SUM(Certificates.Cert)/(SELECT COUNT(Customer) FROM Customer x WHERE x.Customer  = [Customer].[Customer])
FROM         [Customer]
inner JOIN Certificates ON [Customer].[Customer] = Certificates.[Customer]
WHERE [Customer].[Customer] = '570715000000038266'
GROUP BY [Customer].[Customer]
0
pivarCommented:
Or why not


SELECT     [Customer], SUM(Certificates.Cert)
FROM Certificates
WHERE [Customer] = '570715000000038266'
GROUP BY [Customer]
0
DavidMorrisonCommented:
Pivar, I hate to be critical of another professional but are you even reading what OP has asked for?


0
pivarCommented:
Please be critical, but please also be clear of what you referring to. I may have misunderstood the question. But right now I don't understand what you getting at.  
0
dwe761Software EngineerCommented:
Madsing,
It might help us to know what question you are actually trying to answer with your query?

I'm not sure where you got 12750 unless it is the absolute value of all of the certs.  From the data you've shown us, the 850 you want is actually the only cert that does not have a matching negative cert.

So if you just want to find the sum of certs for a single Customer, it seems you'd want something like this:


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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwe761Software EngineerCommented:
Oh, I see where you got 12750.  Since you have 15 rows in your Customer table and only one row in your Certs table that does not have a matching negative cert, your query found those 15 matches on 850 and you asked it to sum them which it gave you 12750.  All other rows cancelled themselves out because they all had matching positive and negative numbers on the same cert.

So even though my query will give you what you want for a single customer, I'm still not sure that's what your original intent was.  If you remove the WHERE clause on my query, you'll just get the sum of all positive certs without matching negative certs which may be kind of meaningless.  You'll have to answer that.  Are you looking for all customer's certs that do not have a matching negative cert?

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.