vendredi 30 janvier 2015

WHY? UNION ALL is doing a Calculation

I have two data sets: DS_A and DS_B.


My question is why I have 2 quantity for pro_id 71549 and not 3?


The ALL Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed.



loc_id pro_id quantity price
------------- ----------- ----------- -----------
2310 5052 1 0
2365 5433 1 0
2310 7694 1 0
2310 9480 1 0
2310 9502 1 0
2310 14413 1 0
2310 31277 1 0
2310 46180 1 0
2310 65233 1 0
2310 68369 1 0
2310 68372 1 0
2310 77396 1 0


loc_id pro_id quantity price
------------- ----------- ----------- -----------
2310 71549 3 0


When I do my UNION ALL



select loc_id
, pro_id
, sum(quantity)
, price
from DS_A
group by loc_id, pro_id
UNION ALL
select 2310
, 71549
, sum(quantity)
, price
from DS_B
group by pro_id, loc_id


Results:



loc_id pro_id quantity price
------------- ----------- ----------- -----------
2310 5052 1 0
2365 5433 1 0
2310 7694 1 0
2310 9480 1 0
2310 9502 1 0
2310 14413 1 0
2310 31277 1 0
2310 46180 1 0
2310 65233 1 0
2310 68369 1 0
2310 68372 1 0
2310 77396 1 0
2310 71549 2 0

Aucun commentaire:

Enregistrer un commentaire