asked on 9/6/2011

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!?

Microsoft SQL Server 2008

Last Comment

dwe761

9/6/2011

pivar

9/6/2011

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

Madsing

9/6/2011

ASKER

Then I get:

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

So that is no good.

DavidMorrison

9/6/2011

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]

pivar

9/6/2011

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]

pivar

9/6/2011

Or why not

SELECT [Customer], SUM(Certificates.Cert)
FROM Certificates
WHERE [Customer] = '570715000000038266'
GROUP BY [Customer]

DavidMorrison

9/6/2011

Pivar, I hate to be critical of another professional but are you even reading what OP has asked for?

pivar

9/6/2011

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.

ASKER CERTIFIED SOLUTION

dwe761

9/6/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

dwe761

9/6/2011

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?