?
Solved

Problem with dublicates and SUM Sql Query

Posted on 2011-09-06
9
1 solution
Medium Priority
?
208 Views
Last Modified: 2012-05-12
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!?
0
Comment
Question by:Madsing
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Join The Community
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 36488101
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
 

Author Comment

by:Madsing
ID: 36488121
Then I get:

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

So that is no good.
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36488206
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Learn more
 
LVL 22

Expert Comment

by:pivar
ID: 36488207
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
 
LVL 22

Expert Comment

by:pivar
ID: 36488213
Or why not


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

Expert Comment

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


0
 
LVL 22

Expert Comment

by:pivar
ID: 36488259
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
 
LVL 10

Accepted Solution

by:
dwe761 earned 2000 total points
ID: 36488495
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
 
LVL 10

Expert Comment

by:dwe761
ID: 36488529
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

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Watch the Video
Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Tell Who Has Access to What in SQL Server
Article by: Greg
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Encryption - Decryption in SQL Server 2008 and backup database with encrypted data and restore it somewhere else.
Article by: RiteshShah
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
How to view most utilized nodes in your network?
Video by: AdRem
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
How To Make a Graph with Microsoft Access
Video by: crystal
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Advertise Here

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Advertise Here