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

Question

I have a MySQL 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...

Because the groups overlap, it appears 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;

The problem is the above produces a result set in columns, but I am trying to produce a result set in rows.

The traditional way in MySQL to pivot columns to rows is to use a sequence of select sum() from table union repetitively, but in this case the base data is a derived table, so that method seems to not work here. Any ideas of how to flip the columns to rows?

http://sqlfiddle.com/#!9/0f741/3

Answer 1

You can do this either by pivoting the rows after the calculation or by doing the calculation on separate rows. The first should have much better performance:

select x.which,
       (case when x.n = 1 then trans_1
             when x.n = 2 then trans_5
             when x.n = 3 then trans_10
             when x.n = 4 then trans_20
        end) as numtransactions
from (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
     ) d join
     (select 1 as n, '1 or more' as which union all
      select 2, '5 or more' union all
      select 3, '10 or more' union all
      select 4, '20 or more'
     ) x
order by x.n;