I have a doubt when usign extract(year_month).
Follow below my query:
SELECT products.sku, SUM(sells_products.quantity) AS quantity, EXTRACT(YEAR_MONTH FROM sells.date) AS period FROM sells, sells_products, products where sells.id = sells_products.id_sell AND (products.sku = '1111' or products.sku = '2222') AND sells_products.id_product = products.id AND sells.date BETWEEN '2023-05-01' AND '2023-07-12' GROUP BY products.sku, EXTRACT(YEAR_MONTH FROM sells.date)
Look below my result:
SKU | QNT| PERIOD
1111 | 13 | 202305
1111 | 44 | 202306
1111 | 24 | 202307
2222 | 64 | 202305
2222 | 12 | 202306
2222 | 56 | 202307
So, I need make a query that each period (year and month) show a columns in result query, example below:
SKU | 202305 | 202306 | 202307
1111 | 13 | 44 | 24
2222 | 64 | 12 | 56
However, I not want use if case in query, because I need that query make the columns automatically from my period date in query:
sells.date BETWEEN '2023-05-01' AND '2023-07-12'
Thanks!
Sorry, that’s the way SQL works. All columns in the select-list must be fixed when you prepare the query. The query can’t make more columns automatically during execution.
Is this a PHP problem, or a MySQL problem?
After getting query, using PHP+array is allowed ? With an associative array, you can easily create a table as you need.