SQL Distinct Sum

Question

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 1

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.