How do I write this GROUP BY in mysql UNION query
- by user1652368
Trying to group the results of two queries together. When I run this query:
SELECT pr_id, pr_sbtcode, pr_sdesc, od_quantity, od_amount
FROM
(
SELECT `bgProducts`.`pr_id`, `bgProducts`.`pr_sbtcode`,
`bgProducts`.`pr_sdesc`, SUM(`od_quantity`) AS `od_quantity`,
SUM(`od_amount`) AS `od_amount`,
MIN(UNIX_TIMESTAMP(`or_date`)) AS `or_date`
FROM `bgOrderMain`
JOIN `bgOrderData`
JOIN `bgProducts`
WHERE `bgOrderMain`.`or_id` = `bgOrderData`.`or_id`
AND `od_pr` = `pr_id`
AND UNIX_TIMESTAMP(`or_date`) >= '1262322000'
AND UNIX_TIMESTAMP(`or_date`) <= '1346990399'
AND (`pr_id` = '415' OR `pr_id` = '1088')
GROUP BY `bgProducts`.`pr_id`
UNION
SELECT `bgProducts`.`pr_id`, `bgProducts`.`pr_sbtcode`,
`bgProducts`.`pr_sdesc`,SUM(`od_quantity`) AS `od_quantity`,
SUM(`od_amount`) AS `od_amount`,
MIN(UNIX_TIMESTAMP(`or_date`)) AS `or_date`
FROM `npOrderMain`
JOIN `npOrderData`
JOIN `bgProducts`
WHERE `npOrderMain`.`or_id` = `npOrderData`.`or_id`
AND `od_pr` = `pr_id`
AND UNIX_TIMESTAMP(`or_date`) >= '1262322000'
AND UNIX_TIMESTAMP(`or_date`) <= '1346990399'
AND (`pr_id` = '415' OR `pr_id` = '1088')
GROUP BY `bgProducts`.`pr_id`
) TEMPTABLE3;
it produces this result
+-------+------------+--------------------------+-------------+-----------+
| pr_id | pr_sbtcode | pr_sdesc | od_quantity | od_amount
+-------+------------+--------------------------+-------------+-----------+
| 415 | NP13 | Product 13 | 5 | 125
| 1088 | NPAW | Product AW | 4 | 100
| 415 | NP13 | Product 13 | 5 | 125
| 1088 | NPAW | Product AW | 2 | 50
+-------+------------+--------------------------+-------------+-----------+</pre>
What I want to get a result that combines those into 2 lines:
+-------+------------+--------------------------+-------------+-----------+
| pr_id | pr_sbtcode | pr_sdesc | od_quantity | od_amount
+-------+------------+--------------------------+-------------+-----------+
| 415 | NP13 | Product 13 | 10 | 250
| 1088 | NPAW | Product AW | 6 | 150
+-------+------------+--------------------------+-------------+-----------+</pre>
So I added GROUP BY pr_id to the end of the query:
SELECT pr_id, pr_sbtcode, pr_sdesc, od_quantity, od_amount FROM (
SELECT `bgProducts`.`pr_id`, `bgProducts`.`pr_sbtcode`, `bgProducts`.`pr_sdesc`, SUM(`od_quantity`) AS `od_quantity`, SUM(`od_amount`) AS `od_amount`, MIN(UNIX_TIMESTAMP(`or_date`)) AS `or_date` FROM `bgOrderMain` JOIN `bgOrderData` JOIN `bgProducts` WHERE `bgOrderMain`.`or_id` = `bgOrderData`.`or_id` AND `od_pr` = `pr_id` AND UNIX_TIMESTAMP(`or_date`) >= '1262322000' AND UNIX_TIMESTAMP(`or_date`) <= '1346990399' AND (`pr_id` = '415' OR `pr_id` = '1088') GROUP BY `bgProducts`.`pr_id`
UNION
SELECT `bgProducts`.`pr_id`, `bgProducts`.`pr_sbtcode`, `bgProducts`.`pr_sdesc`,SUM(`od_quantity`) AS `od_quantity`, SUM(`od_amount`) AS `od_amount`, MIN(UNIX_TIMESTAMP(`or_date`)) AS `or_date` FROM `npOrderMain` JOIN `npOrderData` JOIN `bgProducts` WHERE `npOrderMain`.`or_id` = `npOrderData`.`or_id` AND `od_pr` = `pr_id` AND UNIX_TIMESTAMP(`or_date`) >= '1262322000' AND UNIX_TIMESTAMP(`or_date`) <= '1346990399' AND (`pr_id` = '415' OR `pr_id` = '1088') GROUP BY `bgProducts`.`pr_id`
) TEMPTABLE3
GROUP BY pr_id;
But that just gives me this:
+-------+------------+--------------------------+-------------+-----------+
| pr_id | pr_sbtcode | pr_sdesc | od_quantity | od_amount
+-------+------------+--------------------------+-------------+-----------+
| 415 | NP13 | Product 13 | 5 | 125
| 1088 | NPAW | Product AW | 4 | 100
+-------+------------+--------------------------+-------------+-----------+
What am I missing here??
