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!?
Our community of experts have been thoroughly vetted for their expertise and industry experience.