0

In the new OpenCart 2 system is the following sales report query:

        SELECT  MIN(o.date_added) AS date_start,
            MAX(o.date_added) AS date_end,
            COUNT(*) AS orders,
            ( SELECT   SUM(op.quantity)
              FROM     fc_order_product op
              WHERE    op.order_id = o.order_id
              GROUP BY op.order_id
            ) AS products,
            (SELECT   SUM(ot.value)
              FROM     fc_order_total ot
              WHERE    ot.order_id = o.order_id
              AND      ot.code = 'tax'
              GROUP BY ot.order_id
             ) AS tax,
             SUM(o.total) AS total
    FROM     fc_order o
    WHERE    o.order_status_id > 0
    AND      DATE(o.date_added) >= '2014-12-01'
    AND      DATE(o.date_added) <= '2014-12-22'
    GROUP BY WEEK(o.date_added)
    ORDER BY o.date_added DESC

This is pretty fast but with wrong results concerning the tax and products sum.

The old query

        SELECT MIN(tmp.date_added) AS date_start,
           MAX(tmp.date_added) AS date_end,
           COUNT(tmp.order_id) AS orders,
           SUM(tmp.products) AS products,
           SUM(tmp.tax) AS tax,
           SUM(tmp.total) AS total
    FROM  (SELECT o.order_id,
                    (SELECT   SUM(op.quantity)
                       FROM     fc_order_product op
                       WHERE    op.order_id = o.order_id
                       GROUP BY op.order_id
                            ) AS products,
                    (SELECT   SUM(ot.value)
                       FROM     fc_order_total ot
                       WHERE    ot.order_id = o.order_id
                       AND      ot.code = 'tax'
                       GROUP BY ot.order_id
                               ) AS tax,
                   o.total,
                   o.date_added
           FROM     fc_order o
           WHERE    o.order_status_id > 0
           AND      DATE(o.date_added) >= '2014-12-01'
           AND      DATE(o.date_added) <= '2014-12-22'
           GROUP BY o.order_id
          ) tmp
    GROUP BY WEEK(tmp.date_added)
    ORDER BY tmp.date_added

gives the right result but takes about 100 seconds. Does anyone try to solve this problem with me?

HDP
  • 4,005
  • 2
  • 36
  • 58
Ronny Linsener
  • 253
  • 3
  • 16
  • If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry Dec 22 '14 at 22:01
  • Well it's a little bit ticky because only the order-tables from the database have more then 60MB. – Ronny Linsener Dec 22 '14 at 22:18

0 Answers0