I have transaction data like this example:
x
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.
Advertisement
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;