MySQL UNION ALL Derived Table with GROUP BY Not Working

Question

I've been scratching my head on this for days! I made the query below to combine two subqueries and group them by date. The first query in the derived table returns 50 rows, while the second query returns 2 rows.

But if I run the whole thing, it only returns the 50 rows from the FIRST query. AND whatever should have been combined from the second query IS NOT combined at all to the rows with the same GROUP BY column value.

SELECT  tot.payment_date AS "Payment Period",
        tot.total_sales AS "Total Sales"
FROM
    (
        SELECT  DATE_FORMAT(a.payment_dt, "%c/%d/%Y") AS payment_date,
                CAST((SUM(b.ucost * b.qty) + a.shipping_fee) AS DECIMAL(15,2)) AS total_sales
        FROM    tbl_encash_order_sum a
        INNER JOIN tbl_encash_order_det b
            ON a.accid = b.accid AND a.so_no = b.so_no
        WHERE   a.payment_stat = 1
        GROUP BY DATE_FORMAT(a.payment_dt, "%c/%d/%Y")

        UNION ALL

        SELECT  DATE_FORMAT(d.dp_settled_dt, "%c/%d/%Y") AS payment_date,
                SUM(d.order_total) AS total_sales
        FROM    wp_posts c
        INNER JOIN
            (
                SELECT  post_id,
                        MAX(CASE WHEN meta_key = "_payment_status" THEN CAST(meta_value AS SIGNED) END) AS payment_status,
                        MAX(CASE WHEN meta_key = "_order_total" THEN CAST(meta_value AS DECIMAL(15,2)) END) AS order_total,
                        MAX(CASE WHEN meta_key = "_dp_settled_dt" THEN CAST(meta_value AS DATETIME) END) AS dp_settled_dt
                FROM    wp_postmeta
                GROUP BY post_id
            ) d
            ON c.ID = d.post_id
        WHERE c.post_type = "shop_order" AND d.payment_status = 1
        GROUP BY DATE_FORMAT(d.dp_settled_dt, "%c/%d/%Y")
    ) tot
GROUP BY tot.payment_date
ORDER BY tot.payment_date

Answer 1

Here:

SELECT  tot.payment_date AS "Payment Period",
        tot.total_sales AS "Total Sales"

you should be doing SUM(tot.total_sales). Without the sum, it will return an arbitrary one of the total sales for each payment date.

You can make mysql give an error instead of choosing arbitrary data to return by enabling the ONLY_FULL_GROUP_BY sqlmode.