Im using 10.11.8-MariaDB on server.
Here db table schema;
id | productid | date | stone | wood |
---|---|---|---|---|
78 | 113 | 1721638800 | [{“id”:”2″,”spend”:”17,62″,”cost”:”368,557″},{“id”:”3″,”spend”:”1,73″,”cost”:”36,186″}] | [{“id”:”2″,”spend”:”60,62″,”cost”:”1303,330″}] |
79 | 113 | 1721638800 | [{“id”:”2″,”spend”:”16,62″,”cost”:”360,557″},{“id”:”3″,”spend”:”1,73″,”cost”:”36,186″}] | [{“id”:”2″,”spend”:”60,62″,”cost”:”1303,330″}] |
80 | 117 | 1721811600 | [{“id”:”4″,”spend”:”5,62″,”cost”:”150,557″}] | [{“id”:”4″,”spend”:”0″,”cost”:”0″}] |
Im using this sample php codes;
<code>SELECT
e.productid,
h.id AS stone_id,
SUM(CAST(REPLACE(h.spend, ',', '.') AS DECIMAL(20, 6))) AS stone_spend,
SUM(CAST(REPLACE(h.cost, ',', '.') AS DECIMAL(20, 6))) AS stone_cost,
h2.id AS wood_id,
SUM(CAST(REPLACE(h2.spend, ',', '.') AS DECIMAL(20, 6))) AS wood_spend,
SUM(CAST(REPLACE(h2.cost, ',', '.') AS DECIMAL(20, 6))) AS wood_cost
FROM
production e
LEFT JOIN JSON_TABLE(
IFNULL(NULLIF(e.stone, ''), '[]'),
'$[*]' COLUMNS (
id INT PATH '$.id',
spend VARCHAR(20) PATH '$.spend',
cost VARCHAR(20) PATH '$.cost'
)
) AS h ON 1=1
LEFT JOIN JSON_TABLE(
IFNULL(NULLIF(e.wood, ''), '[]'),
'$[*]' COLUMNS (
id INT PATH '$.id',
spend VARCHAR(20) PATH '$.spend',
cost VARCHAR(20) PATH '$.cost'
)
) AS h2 ON h.id = h2.id
WHERE
e.date BETWEEN '$first' AND '$last'
GROUP BY
e.productid, h.id, h2.id;
</code>
<code>SELECT
e.productid,
h.id AS stone_id,
SUM(CAST(REPLACE(h.spend, ',', '.') AS DECIMAL(20, 6))) AS stone_spend,
SUM(CAST(REPLACE(h.cost, ',', '.') AS DECIMAL(20, 6))) AS stone_cost,
h2.id AS wood_id,
SUM(CAST(REPLACE(h2.spend, ',', '.') AS DECIMAL(20, 6))) AS wood_spend,
SUM(CAST(REPLACE(h2.cost, ',', '.') AS DECIMAL(20, 6))) AS wood_cost
FROM
production e
LEFT JOIN JSON_TABLE(
IFNULL(NULLIF(e.stone, ''), '[]'),
'$[*]' COLUMNS (
id INT PATH '$.id',
spend VARCHAR(20) PATH '$.spend',
cost VARCHAR(20) PATH '$.cost'
)
) AS h ON 1=1
LEFT JOIN JSON_TABLE(
IFNULL(NULLIF(e.wood, ''), '[]'),
'$[*]' COLUMNS (
id INT PATH '$.id',
spend VARCHAR(20) PATH '$.spend',
cost VARCHAR(20) PATH '$.cost'
)
) AS h2 ON h.id = h2.id
WHERE
e.date BETWEEN '$first' AND '$last'
GROUP BY
e.productid, h.id, h2.id;
</code>
SELECT
e.productid,
h.id AS stone_id,
SUM(CAST(REPLACE(h.spend, ',', '.') AS DECIMAL(20, 6))) AS stone_spend,
SUM(CAST(REPLACE(h.cost, ',', '.') AS DECIMAL(20, 6))) AS stone_cost,
h2.id AS wood_id,
SUM(CAST(REPLACE(h2.spend, ',', '.') AS DECIMAL(20, 6))) AS wood_spend,
SUM(CAST(REPLACE(h2.cost, ',', '.') AS DECIMAL(20, 6))) AS wood_cost
FROM
production e
LEFT JOIN JSON_TABLE(
IFNULL(NULLIF(e.stone, ''), '[]'),
'$[*]' COLUMNS (
id INT PATH '$.id',
spend VARCHAR(20) PATH '$.spend',
cost VARCHAR(20) PATH '$.cost'
)
) AS h ON 1=1
LEFT JOIN JSON_TABLE(
IFNULL(NULLIF(e.wood, ''), '[]'),
'$[*]' COLUMNS (
id INT PATH '$.id',
spend VARCHAR(20) PATH '$.spend',
cost VARCHAR(20) PATH '$.cost'
)
) AS h2 ON h.id = h2.id
WHERE
e.date BETWEEN '$first' AND '$last'
GROUP BY
e.productid, h.id, h2.id;
My php codes working but The grand total is being made. What I want to do is separate expenses and costs based on ID.
I want aggregate on sql. If they have the same date and product id, grouping the id in the json and adding the spend and cost should give the following result;
<code>[
{
"productid": "113",
"date": "1721638800",
"stone": [
{
"id": "2",
"spend": "34,24",
"cost": "729,114"
},
{
"id": "3",
"spend": "3,46",
"cost": "72,372"
}
],
"wood": [
{
"id": "2",
"spend": "121,24",
"cost": "2606,66"
}
]
},
{
"productid": "117",
"date": "1721638800",
"stone": [
{
"id": "4",
"spend": "5,62",
"cost": "150,557"
}
],
"wood": [
{
"id": "4",
"spend": "0",
"cost": "0"
}
]
}
]
</code>
<code>[
{
"productid": "113",
"date": "1721638800",
"stone": [
{
"id": "2",
"spend": "34,24",
"cost": "729,114"
},
{
"id": "3",
"spend": "3,46",
"cost": "72,372"
}
],
"wood": [
{
"id": "2",
"spend": "121,24",
"cost": "2606,66"
}
]
},
{
"productid": "117",
"date": "1721638800",
"stone": [
{
"id": "4",
"spend": "5,62",
"cost": "150,557"
}
],
"wood": [
{
"id": "4",
"spend": "0",
"cost": "0"
}
]
}
]
</code>
[
{
"productid": "113",
"date": "1721638800",
"stone": [
{
"id": "2",
"spend": "34,24",
"cost": "729,114"
},
{
"id": "3",
"spend": "3,46",
"cost": "72,372"
}
],
"wood": [
{
"id": "2",
"spend": "121,24",
"cost": "2606,66"
}
]
},
{
"productid": "117",
"date": "1721638800",
"stone": [
{
"id": "4",
"spend": "5,62",
"cost": "150,557"
}
],
"wood": [
{
"id": "4",
"spend": "0",
"cost": "0"
}
]
}
]
I need fix problem of my codes.