Generate a report of Custom Fees charged by order date

report-custom-fees-by-order-date.sql

SELECT
    DATE(so.created_at) AS 'Order Date',
    fee.title AS 'Fee',
    SUM(fee.base_value) AS 'Base Total Amount',
    SUM(fee.value) AS 'Total Amount'
FROM custom_order_fees AS cof
JOIN JSON_TABLE(
    cof.custom_fees,
    '$' COLUMNS (
        NESTED PATH '$.*' COLUMNS (
            title VARCHAR(255) PATH '$.title',
            value DECIMAL(10, 4) PATH '$.value',
            base_value DECIMAL(10, 4) PATH '$.base_value'
        )
    )
) AS fee
LEFT JOIN sales_order AS so ON so.entity_id = cof.order_entity_id
GROUP BY DATE(so.created_at), fee.title;