SQL groups inside groups

Question

I have transaction data like this example:

Name    | Price
George  | 20
George  | 20
George  | 20
George  | 30
Paul    | 20
Paul    | 20
Paul    | 30
Paul    | 30
Paul    | 35

I need to group by user and sort by the number of transactions in general, but within that group of users also make groups by price ordering by the amount of transactions at that price. I need this result:

Name    | Price | Count
Paul    | 20    | 2
Paul    | 30    | 2
Paul    | 35    | 1
George  | 20    | 3
George  | 30    | 1

UPDATE

It is in a MySQL 5.5 database. I need to make the query for fluent in Laravel but having it in SQL is a great advance.

Thanks in advance.

Answer 1

SELECT t1.*
FROM ( SELECT name, 
              price, 
              COUNT(*) cnt
       FROM srctable
       GROUP BY name, price ) t1
JOIN ( SELECT name, 
              COUNT(*) tcnt
       FROM srctable
       GROUP BY name ) t2 USING (name)
ORDER BY tcnt DESC, 
         cnt DESC;

fiddle

Answer 2

Here you go. It can be done in MySQL 8.x. The double ordering you want requires the use of a couple of table expressions, as shown below:

select
  x.name, x.price, x.cnt
from (
  select name, price, count(*) as cnt
  from t
  group by name, price
) x
join (
  select name, row_number() over(order by cnt desc) as ob
  from (
    select name, count(*) as cnt
    from t
    group by name
  ) y
) z on x.name = z.name
order by z.ob, x.cnt desc

Result:

name    price  cnt
------  -----  ---
Paul       20    2
Paul       30    2
Paul       35    1
George     20    3
George     30    1

For reference, the data script I used is:

create table t (
  name varchar(10),
  price int
);

insert into t (name, price) values
  ('George', 20),
  ('George', 20),
  ('George', 20),
  ('George', 30),
  ('Paul', 20),
  ('Paul', 20),
  ('Paul', 30),
  ('Paul', 30),
  ('Paul', 35);