CODEWITHSUNDEEP
mysqlunionunion-all
swiss_blade
swiss_blade

Reputation: 541

MySQL UNION does not seem to work correctly

I have an SQL query I am using to pull data from an orders database. I am querying 2 tables and combining the results using UNION ALL. However, the UNION ALL does not seem to work as expected. Here is the query I am using:

SELECT year(oc_order.date_added) AS year, COUNT(oc_order.order_id) as cnt, SUM( ifnull(oc_order.new_total,oc_order.total) ) as total
FROM oc_order
WHERE oc_order.order_status_id IN (1,3,5)
    AND MONTH(oc_order.date_added) BETWEEN '01' AND '02'
    AND DAY(oc_order.date_added) BETWEEN '01' AND '31'
GROUP BY year(oc_order.date_added)

UNION ALL

SELECT ifnull(year(str_to_date(oc_return_custom.date_added,'%d-%m-%Y %H:%i:%s')),year(str_to_date(oc_return_custom.date_added,'%Y-%m-%d %H:%i:%s')) ) AS year, COUNT(oc_return_custom.return_id) as cnt, SUM( oc_return_custom.total ) as total
FROM oc_return_custom
WHERE ifnull(MONTH(str_to_date(oc_return_custom.date_added,'%d-%m-%Y %H:%i:%s')),MONTH(str_to_date(oc_return_custom.date_added,'%Y-%m-%d %H:%i:%s')) ) BETWEEN '01' AND '02'
    AND ifnull(DAY(str_to_date(oc_return_custom.date_added,'%d-%m-%Y %H:%i:%s')),DAY(str_to_date(oc_return_custom.date_added,'%Y-%m-%d %H:%i:%s')) ) BETWEEN '01' AND '31'
GROUP BY ifnull(year(str_to_date(oc_return_custom.date_added,'%d-%m-%Y %H:%i:%s')),year(str_to_date(oc_return_custom.date_added,'%Y-%m-%d %H:%i:%s')) )
ORDER BY year DESC

This is what I get from the query:

+=======+========+=======+
| year  | cnt    | total |
+=======+========+=======+
| 2016  |  200   |  1000 |
| 2016  |   50   |   200 |
| 2015  |  100   |   800 |
| 2015  |   10   |    50 |
+=======+========+=======+

But this is what I wanted to get:

+=======+========+=======+
| year  | cnt    | total |
+=======+========+=======+
| 2016  |  250   |  1200 |
| 2015  |  110   |   850 |
+=======+========+=======+

Can someone tell me what I am doing wrong???

Notes: The oc_order table's date_added column is datetime whereas oc_return_custom 's date_added column is just text.

Upvotes: 0

Views: 58

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

UNION ALL simply puts together two data sets produced by separate GROUP BY operations.

To get the expected result set you have to wrap the query in a subquery and apply an additional GROUP BY:

SELECT year, SUM(cnt) AS cnt, SUM(total) AS total
FROM ( ... your query here ...) AS t
GROUP BY year

Upvotes: 2

Related Questions