How to find the repeat customers in the list?-CodePudding

I Have Purchase Table Containing 5 Columns

Columns Names Are

CustomerID, BillID, ProductID, unatity, Payment_Type

Columns Values Are

CID00001, BID00001, PID001, 1, Card

Total Customers Count - 37156

DISTINCT Customers Count - 26053

How to Find the repeat Customers? (37156 - 26053 = 11103)

CodePudding user response：

Aggregation is one way:

SELECT COUNT(*) AS num_repeat
FROM
(
    SELECT CustomerID
    FROM purchases
    GROUP BY CustomerID
    HAVING COUNT(*) > 1
) t;

CodePudding user response：

To get the list of repeat customers,

SELECT CustomerID, COUNT(*) AS PurchaseCount
FROM purchases
GROUP BY CustomerID
HAVING COUNT(*) > 1

CodePudding user response：

You can use this :

SELECT * FROM Purchase 
WHERE CustomerID 
IN(
    SELECT CustomerID FROM Purchase
    GROUP BY CustomerID HAVING COUNT(*) > 1
    )