SQL Server - Get the Total Count with the TOP 1 product

Question

I need to be able to find the total count of orders placed by a customer, but also find the top product in one query. 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 have come up with a query,

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, that is not giving me the result I am looking for, for Paul it is giving me the correct ProductId, but a count of that product alone.

I want the a Query to return,

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

score 2 · Accepted Answer · answered Feb 03 '17 at 10:26

One option is with 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

score 0 · Answer 2 · answered Feb 03 '17 at 10:33

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

score 0 · Answer 3 · edited May 23 '17 at 10:29

Similar question has been answered with a nice explanation here

(Benefit here : common concept of join used). (disadvantage : query may not be efficient for large records) I have modified 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

cloudsafe · Answer 4 · 2017-02-03T11:10:06.413

Works on 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

score -1 · Answer 5 · answered Feb 03 '17 at 10:49

If you can't use the over clause, this would work (obviously, a lot more work compared to an 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 |
+--------+----------+-----------+----------+

SQL Server - Get the Total Count with the TOP 1 product

5 Answers5