MySQL Group by aggregate functions

Sorry if i sound total newbie but i know im one step away from solving this problem. Is there any way that i can GROUP BY the number_of_trans? i would like to know how many users have the same number of transaction, grouped by the number of transaction. my query is like this:

SELECT
  cust_id, COUNT(*) AS number_of_trans
FROM
  table
GROUP BY
  cust_id
HAVING COUNT(*) > 1
ORDER BY
  number_of_trans

the result im getting is like this

+------------------+--------------+
| cust_id          | numb_of_trans|
+------------------+--------------+
| 01               |  2           |
| 02               |  3           |
| 03               |  3           |
| 04               |  4           |
| 05               |  4           |
+------------------+--------------+

while im expecting a result like this:

+------------------+--------------+
| numb_of_trans    | count        |
+------------------+--------------+
| 1                |  null        |
| 2                |  1           |
| 3                |  2           |
| 4                |  2           |
| 5                |  null        |
+------------------+--------------+

2 answers

  • answered 2020-02-19 11:01 GMB

    You could left join your current aggregate query with a list of numbers, and aggregate once more:

    select 
        n.number_of_trans,
        count(*) cnt
    from (
        select 1 number_of_trans
        union all select 2
        union all select 3
        union all select 4
        union all select 5
    ) n
    left join (
        select count(*) as number_of_trans
        from mytable
        group by cust_id
        -- having count(*) > 1
    ) t on t.number_of_trans = n.number_of_trans
    group by n.number_of_trans
    order by n.number_of_trans
    

    You can expand the union all subquery with more numbers if needed.

    I am unsure that you do want a having clause in the aggregate query that filters out cust_ids that have just one entry, so I commented it - feel free to uncomment it as needed.

    Also please note that this will yield 0 instead of null for number_of_trans that do not exist in the aggregate subquery. If you really want null, then you can do:

    nullif(count(*), 0) cnt
    

  • answered 2020-02-19 11:03 lion_pankaj

    Can you try below statement.

    SELECT
    number_of_trans, count(number_of_trans) as count FROM table GROUP BY number_of_trans ORDER BY number_of_trans.