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