SQL groups inside groups

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

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

Advertisement

Answer