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.

You're looking for [`GROUP BY`](https://msdn.microsoft.com/en-us/library/ms177673.aspx?f=255&MSPPError=-2147217396). And please please please start using [ANSI syntax](http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) for joins. — BJ Myers, Apr 25 '16 at 01:24

score 2 · Accepted Answer · edited Jul 29 '20 at 18:25

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.

score 0 · Answer 2 · answered Apr 26 '16 at 04:24

I think,it was a good question for discussion.

Correct query always depend upon your actual requirement.

I think your table is too much normalise.In such situation most of them will also keep Employeeid in order_detail table.

At the same time,most of them keep sum value in Order table. Like sum of quantity,sum of amount etc per orderid in order table.

you can also create view without aggregate function joining all the table.

IMHO,Using Group By clause on so many column and that too on varchar column is bad idea.

Try something like this,

;With CTE as
(
 SELECT 
    E.FirstName + ' ' + E.LastName [Full Name], 
     O.OrderID,od.qty,P.ProductName
FROM  Employees E
inner join  Orders O on  E.EmployeeID = O.EmployeeID
inner join [Order Details] OD on o.orderid=od.orderid      
inner join [Products] P on p.ProductID=od.ProductID 
)
,CTE1 as
(
select od.orderid, sum(qty) TotalQty 
from CTE c
group by c.orderid

)

select c.[Full Name],c1.TotalQty, P.ProductName from cte c
inner join cte1 c1 on c.orderid=c1.orderid

SQL Distinct Sum

2 Answers2