SQL how to count the number of credit cards that had at least 1,5,10,20 etc transactions

Question

I have a data set of credit card transactions.

create table trans (
  card_id int,
  amount int
);

insert into trans values (1, 1);
insert into trans values (2, 1);
insert into trans values (3, 1);
insert into trans values (4, 1);
insert into trans values (5, 1);
insert into trans values (5, 1);
insert into trans values (6, 1);
insert into trans values (6, 1);
insert into trans values (7, 1);
insert into trans values (7, 1);
insert into trans values (8, 1);
insert into trans values (8, 1);
insert into trans values (8, 1);
insert into trans values (9, 1);
insert into trans values (9, 1);
insert into trans values (9, 1);
insert into trans values (10, 1);
insert into trans values (10, 1);
insert into trans values (10, 1);
insert into trans values (10, 1);

I desire to know:

1. how many cards were used to make at least 1 transaction
2. how many cards were used to make at least 5 transactions
3. how many cards were used to make at least 10 transactions
4. how many cards were used to make at least 20 transactions
etc...

SQL:

select count, sum(count2) from
  (
  select count, count(*) count2 from
    (
    select card_id, count(*) count
    from trans
    group by card_id 
    ) d
  group by count
  ) d2
where count> {is at least __} /*this is the part causing an error*/
group by count
order by count

You have an error in your SQL syntax...

http://sqlfiddle.com/#!9/705b5/5

Try something like this http://sqlfiddle.com/#!9/705b5/16 – Aleksandar Miladinovic Apr 13 '15 at 19:11 — Aleksandar Miladinovic, Apr 13 '15 at 19:11

score 1 · Accepted Answer · answered Apr 13 '15 at 19:19

Because the groups overlap, I think conditional aggregation is a better approach:

select sum(cnt >= 1) as trans_1,
       sum(cnt >= 5) as trans_5,
       sum(cnt >= 10) as trans_10,
       sum(cnt >= 20) as trans_20
from (select card_id, count(*) as cnt
      from trans
      group by card_id 
      ) d;

SQL how to count the number of credit cards that had at least 1,5,10,20 etc transactions

1 Answers1