SQL Server - Get Total with Product TOP 1

Question

I need to find the total number of orders placed by the client, but also find the top product in one request. For example, in the following structure

CREATE TABLE #Cust (CustId INT, CustName VARCHAR(50)) CREATE TABLE #Product (ProductId INT, ProductName VARCHAR(10) ) CREATE TABLE #Orders (CustId INT, ProductId INT, OrderTaken BIT) INSERT #Cust ( CustId, CustName ) VALUES ( 1, 'Paul' ), ( 2, 'F' ), ( 3, 'Francis' ) INSERT #Product ( ProductId, ProductName ) VALUES ( 1, 'Table' ), ( 2, 'Chair' ) INSERT #Orders ( CustId, ProductId, OrderTaken ) VALUES ( 1, 1, 1 ), ( 1, 1, 1 ), ( 1, 2, 1 ), ( 2, 1, 1 ) 

I came up with a request,

 SELECT * FROM #Cust AS C OUTER APPLY ( SELECT TOP 1 SQ.ProductId, SUM(SQ.TotalCount) AS TotalQty FROM ( SELECT O.ProductId, COUNT(*) TotalCount FROM #Orders AS O WHERE O.CustId = C.CustId GROUP BY O.CustId , O.ProductId ) SQ GROUP BY SQ.ProductId ) X 

But this does not give me the result that I am looking for, because Paul gives me the correct ProductId, but only the account of this product.

I want the request to be returned

 CustId | CustName | ProductId | TotalQty --------+---------------+---------------+------------ 1 | Paul | 1 | 3 2 | F | 1 | 1 3 | Francis | NULL | NULL 

Answer 1

One option is the WITH TiES clause

 Select Top 1 with ties CustID ,CustName ,ProductId ,TotalQty From ( Select C.CustID ,C.CustName ,O.ProductId ,TotalQty = count(O.CustId) over (Partition By O.CustID) ,ProdCount = count(O.CustId) over (Partition By O.CustID,O.ProductID) From #Cust C Left Join #Orders O on C.CustID=O.CustId ) A Order by Row_Number() over (Partition By CustID Order by ProdCount Desc) 

Returns

 CustID CustName ProductId TotalQty 1 Paul 1 3 2 F 1 1 3 Francis NULL 0 

Answer 2

Try

 SELECT c.*, ProductId, CustProdTotal, CustTotal FROM #Cust AS C OUTER APPLY ( select top(1) with ties ProductId, CustProdTotal, CustTotal from ( select *, count(OrderTaken) over() as CustTotal , count(OrderTaken) over(partition by ProductId) as CustProdTotal from #Orders o where O.CustId = C.CustId) x order by row_number() over(order by CustProdTotal desc) ) z 

Answer 3

A nice explanation has been sent to this question here.

(Benefit here: general concept of using a compound). (flaw: the request may be ineffective for large records) I changed the solution for your scenario

  SELECT s.CustId, s.CustName, s.ProductId, m.TotalOrderTaken FROM (SELECT p.CustId, p.CustName, t.ProductId, COUNT(*) AS ProductIdCount FROM #Cust AS p JOIN #Orders AS t ON p.CustId = t.CustId GROUP BY p.CustId, p.CustName, t.ProductId ) AS s JOIN (SELECT s.CustId, MAX(s.ProductIdCount) AS MaxProductIdCount, sum(s.ProductIdCount) TotalOrderTaken FROM ( SELECT p.CustId,p.CustName, t.ProductId, COUNT(*) AS ProductIdCount FROM #Cust AS p JOIN #Orders AS t ON p.CustId = t.CustId GROUP BY p.CustId, p.CustName, t.ProductId ) AS s GROUP BY s.CustId ) AS m ON s.CustId = m.CustId AND s.ProductIdCount = m.MaxProductIdCount 

Answer 4

Works with SQL Server 2005 onwards.

 ;with cte1 as (select c.custid, c.custname, o.productid, count(*) as TotalQty from #cust c left join #orders o on c.custid=o.custid left join #product p on p.productid=o.productid group by c.custid, c.custname, o.productid) ,cte2 as (select custid, max(TotalQty) as TopQty from cte1 group by custid) Select cte1.* from cte1 inner join cte2 on cte1.custid=cte2.custid and cte1.TotalQty=cte2.Topqty 

Answer 5

If you can't use the over clause, this will work (obviously, a lot more work than the over clause):

 SELECT custOrderAll.CustId , custOrderAll.CustName , MaxOrder.ProductId , MAX(custOrderAll.cntAll) TotalQty FROM ( SELECT c.CustId , c.CustName , COUNT(O.ProductId) cntAll FROM #Cust AS C LEFT JOIN #Orders AS O ON O.CustId = C.CustId GROUP BY c.CustId , c.CustName ) custOrderAll LEFT JOIN ( SELECT custOrderMAX.CustId , custOrderMAX.CustName , custOrderMAX.ProductId FROM ( SELECT c.CustId , c.CustName , O.ProductId , COUNT(O.ProductId) cntMax FROM #Cust AS C LEFT JOIN #Orders AS O ON O.CustId = C.CustId GROUP BY c.CustId , c.CustName , O.ProductId ) custOrderMAX INNER JOIN ( SELECT mxCnt.CustId , mxCnt.CustName , MAX(mxCnt.cntMax) mxCnt FROM ( SELECT c.CustId , c.CustName , O.ProductId , COUNT(O.ProductId) cntMax FROM #Cust AS C LEFT JOIN #Orders AS O ON O.CustId = C.CustId GROUP BY c.CustId , c.CustName , O.ProductId ) mxCnt GROUP BY mxCnt.CustId , mxCnt.CustName ) custOrderMAXCnt ON custOrderMAXCnt.CustId = custOrderMAX.CustId AND custOrderMAXCnt.mxCnt = custOrderMAX.cntMax ) MaxOrder ON MaxOrder.CustId = custOrderAll.CustId AND MaxOrder.CustName = custOrderAll.CustName GROUP BY custOrderAll.CustId , custOrderAll.CustName , MaxOrder.ProductId 

Result:

 +--------+----------+-----------+----------+ | CustId | CustName | ProductId | TotalQty | +--------+----------+-----------+----------+ | 1 | Paul | 1 | 3 | | 2 | F | 1 | 1 | | 3 | Francis | NULL | 0 | +--------+----------+-----------+----------+