0

I have a query to summerize my data based on month between year 2021 - next years (depend on the real date) i used simple query like this to summerize the amount monthly

SUM(IF(pt.description like '%O-2101%', pt.amount, 0)) AS JANUARI,
SUM(IF(pt.description like '%O-2102%', pt.amount, 0)) AS FEBRUARI, SUM(IF(pt.description like '%O-2103%', pt.amount, 0)) AS MARET, SUM(IF(pt.description like '%O-2104%', pt.amount, 0)) AS APRIL, SUM(IF(pt.description like '%O-2105%', pt.amount, 0)) AS MEI, SUM(IF(pt.description like '%O-2106%', pt.amount, 0)) AS JUNI, SUM(IF(pt.description like '%O-2107%', pt.amount, 0)) AS JULI, SUM(IF(pt.description like '%O-2108%', pt.amount, 0)) AS AGUSTUS, SUM(IF(pt.description like '%O-2109%', pt.amount, 0)) AS SEPTEMBER, SUM(IF(pt.description like '%O-2110%', pt.amount, 0)) AS OKTOBER, SUM(IF(pt.description like '%O-2111%', pt.amount, 0)) AS NOVEMBER, SUM(IF(pt.description like '%O-2111%', pt.amount, 0)) AS DESEMBER, SUM(IF(pt.description like '%O-2201%', pt.amount, 0)) AS JANUARI, SUM(IF(pt.description like '%O-2202%', pt.amount, 0)) AS FEBRUARI, SUM(IF(pt.description like '%O-2203%', pt.amount, 0)) AS MARET, SUM(IF(pt.description like '%O-2204%', pt.amount, 0)) AS APRIL, SUM(IF(pt.description like '%O-2205%', pt.amount, 0)) AS MEI, SUM(IF(pt.description like '%O-2206%', pt.amount, 0)) AS JUNI, SUM(IF(pt.description like '%O-2207%', pt.amount, 0)) AS JULI, SUM(IF(pt.description like '%O-2208%', pt.amount, 0)) AS AGUSTUS, SUM(IF(pt.description like '%O-2209%', pt.amount, 0)) AS SEPTEMBER, SUM(IF(pt.description like '%O-2210%', pt.amount, 0)) AS OKTOBER, SUM(IF(pt.description like '%O-2211%', pt.amount, 0)) AS NOVEMBER FROM platform.mtr_user mu  
left join `transaction`.pg_trx pt on pt.mtr_user_id = mu.id   
WHERE JSON_CONTAINS(pt.tags, '"purchase"', '$') and pt.status = '1' and pt.description -> '$.callback_sales' is not null
-- and mu.email = '[email protected]' 
GROUP BY mu.email   
order by pt.order_id limit 100;



but i think this query is not dynamic query.
maybe someone can help me to make a simple looping query.
Improve this question
4
  • 2
    While pivot table like expressions look like the presentation, even with this small example shows the complexity of the expression. If data was returned in a month per row it wouldn't need to be dynamic. LIKE expressions on a description is horribly unstructured data and if a date field is in the table that would be much cleaner. Welcome to DBA stack exchange.
    – danblack
    Nov 28, 2022 at 7:13
  • 1
    Use synthetic table which generates (months) numbers list from 1 to 11 (or maybe 12?) and use it as one more base data source. Also use another synthetic table which generates years numbers list.
    – Akina
    Nov 28, 2022 at 8:45
  • so no need to make this query much more simple then my current query @danblack? i can't do that sir @Akina, because the table already created on production. is there any other suggestion?
    – Yuyu Gaming
    Nov 28, 2022 at 10:29
  • 1
    And LIKE '%...' is inefficient. And burying information in JSON is inefficient. And after moving things to columns you will be able to make use of INDEXes.
    – Rick James
    Nov 28, 2022 at 17:25

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.