CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Patthebug
Patthebug

Reputation: 4797

Number of repeating customers in SQL Server

I have a dataset containing Date, customer numbers and Payment type information. A single customer can use different payment types on the same or different Date. I would like to find out the customer numbers of the customers who used more than one payment type. A sample is as follows:

Date    CustNum PaymentType
26/7/2014   1   type1
27/7/2014   2   type1
28/7/2014   3   type1
29/7/2014   4   type2
24/7/2014   2   type2
24/7/2014   4   type2
25/7/2014   6   type3
30/7/2014   9   type3
2/7/2014    1   type3
26/7/2014   4   type4
2/7/2014    2   type4
3/7/2014    6   type4
26/7/2014   3   type5
26/7/2014   4   type5

In the above dataset, one can see that multiple customers have used multiple payment types. I would like to have the customer numbers of all these customers along with the payment type they used and the number of transactions for each payment type.

Here's the desired result:

PaymentType    Custnum    NumTransactions
type1    1    1
type3    1    1
type1    2    1
type2    2    1
type4    2    1
type1    3    1
type5    3    1
type2    4    2
type4    4    1
type5    4    1

Any help in this regard would be much appreciated.

Upvotes: 0

Views: 63

Answers (2)

Lamak
Lamak

Reputation: 70668

Now that you updated your question, here is a way:

SELECT  PaymentType,
        CustNum,
        COUNT(*) NumTransactions
FROM dbo.YourTable A
WHERE EXISTS(SELECT 1
             FROM dbo.YourTable
             WHERE CustNum = A.CustNum
             GROUP BY CustNum
             HAVING MIN(PaymentType) <> MAX(PaymentType))
GROUP BY PaymentType,
         CustNum

Upvotes: 2

Dgan
Dgan

Reputation: 10295

try this:

For

numbers of the customers who used more than one payment type.

select COUNT(PaymentType) CountOfCust,PaymentType,CustNum from Customers
Having COUNT(PaymentType)>1
GRPUP BY PaymentType,CustNum

Upvotes: 0

Related Questions