CODEWITHSUNDEEP
sqlsql-server
Syauqi Razak
Syauqi Razak

Reputation: 9

Count customer that have >10 transaction with us, excluding returns

I have been given the task to find the count of customers that have > 10 transactions. I have tried and the code is show below:

select 
count(*) as Customer_More_10_Transaction
from dbo.Transactions
group by cust_id
having count (*) > 10

The output just show the transaction of customer and not count the customer with > 10 transaction. I expected the output to show 36.

The output shown:

12
11
11
11
12
11
11
12
11
11
12
11
11
11
12
11
12
13
11
11
11
12
11
11
11
12
11
11
11
11
11
11
12
13
11
11

Actual output needed:

36

Upvotes: 0

Views: 2674

Answers (4)

Sriram M K
Sriram M K

Reputation: 11

SELECT COUNT(*)
FROM
(
    SELECT cust_id 
    FROM  dbo.Transactions
    group by cust_id
    having count (*) > 10
) AS T

Get the list of custid with transaction more than 10 and count it.

Upvotes: 1

Praveen Yadav
Praveen Yadav

Reputation: 1

SELECT UserId, COUNT(*)
FROM transactionlogs
WHERE UserId is not null 
GROUP BY UserId HAVING COUNT (*) > 10;

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522244

We can handle your requirement without a subquery:

SELECT TOP 1 COUNT(*) OVER () AS total_cnt
FROM dbo.Transactions
GROUP BY cust_id
HAVING COUNT (*) > 10;

Upvotes: 2

Serkan Arslan
Serkan Arslan

Reputation: 13393

you also need to count your result.

SELECT count(*) as Count_Of_Customer_More_10_Transaction FROM
(
  select 
  count(*) as Customer_More_10_Transaction
  from    dbo.Transactions
  group by cust_id
  having count (*) > 10
) AS T

Upvotes: 3

Related Questions