N3wbie N3wbie - 7 months ago 23
SQL Question

SQL Distinct Sum

SELECT DISTINCT
E.FirstName + ' ' + E.LastName [Full Name],
P.ProductName,
OD.Quantity
FROM Employees E,
Products P,
[Order Details] OD,
Orders O
WHERE
E.EmployeeID = O.EmployeeID
AND O.OrderID = OD.OrderID
AND OD.ProductID = P.ProductID


In the Northwind gives back duplicate
FullNames
and
ProductNames
because of the
Quantity
which is changed (because of the date shipped each time).

I want to present only a Name to a specific
ProductName
with the Total Quantity and not divided.

Answer

You need to use GROUP BY with SUM:

SELECT
    e.FirstName + ' ' + e.LastName AS [Full Name], 
    p.ProductName, 
    SUM(od.Quantity) AS [Quantity]
FROM Employees e
INNER JOIN Orders o
    ON o.EmployeeID = e.EmployeeID
INNER JOIN [Order Details] od
    ON od.OrderID = o.OrderID
INNER JOIN Products p
    ON p.ProductID = od.ProductID
GROUP BY
    e.FirstName + ' ' + e.LastName,
    p.ProductName

Note, you need to stop using the old-style JOIN syntax.