How to use subquery for results in every quarter popular 10 amounts

Question

I need get results for every quarter TOP 10 amounts . Like I have some query:

SELECT extract(YEAR FROM entity_created) quarterY, 
       extract(quarter FROM entity_created) quarter
FROM tabl.tablee
WHERE resolution LIKE 'APPROVED' 
AND entity_created > '2020-01-01'
GROUP BY extract(YEAR FROM entity_created), extract(quarter FROM entity_created)
ORDER BY quarterY, quarter:

And result this:

quartery    quarter
2020               1
2020               2
2020               3
2020               4
2021               1
2021               2
2021               3
2021               4
2022               1

then I have writed other query for TOP 10 amounts:

SELECT amount, 
       count(*) AS countt
FROM tabl.tablee
WHERE entity_created >= '2020-01-01'
GROUP BY amount
ORDER BY countt DESC
LIMIT 10

Result :

amount        countt
100.00        49897
1000.00       39674
500.00        27907
200.00        25991
300.00        22270
15000.0       21642
3000.00       13467
5000.00       13224
2000.00       11896
150.00        11287

How to edit query for make every quarter shows top 10 amounts? Need just put this subquery somewhere?

Results need to be like this : https://notepad.pw/share/zqckt8el

Why dont you replace your tabl.tablee with subquery? Also you need necessary field from sub query — Gurkan İlleez, Mar 22 '22 at 19:42
you should check https://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results — Ergest Basha, Mar 22 '22 at 19:46
Please add some data and expected result. What `MySQL` version are you using ? — Ergest Basha, Mar 22 '22 at 20:06
@ErgestBasha expected result are here : https://notepad.pw/share/zqckt8el data are many amounts, many amounts are the same, and need result top 10 amounts by how much get this amount every quarter. in result link i think you understand what i need — Power Cat, Mar 22 '22 at 20:14
@PowerCat I understand what you need , I need the data that produces the result in your link and table description, too. Are you using `MySQL 8+` ? — Ergest Basha, Mar 22 '22 at 20:35

How to use subquery for results in every quarter popular 10 amounts

0 Answers0