SQL how to count the number of credit cards that had x transactions per month

Question

I have a MySQL data set of credit card transactions:

create table trans (
  transdate date,
  card_id int
);

I desire to know:

1. how many cards were used to make at least 1 transaction, per month
2. how many cards were used to make at least 5 transactions, per month
3. how many cards were used to make at least 10 transactions, per month
4. how many cards were used to make at least 20 transactions, per month
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 total. I would like a result set per month:

year | month | trans_1 | trans_5 | trans_10 | trans_20 | etc
2015 |     1 |       1 |       1 |        0 |        0 | 
2015 |     2 |
2015 |     3 |

I cant figure out how to group by month in this data set.

Answer 1

If you want the values per month, then you need to aggregate by month in the inner and outer queries:

select yr, mon,
       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 year(transdate) as yr, month(transdate) as mon, card_id, count(*) as cnt
      from trans
      group by card_id, year(transdate), month(transdate)
     ) d
group by yr, mon;