Pivot table using External data

[nc]

Hi

I am generating a pivot table using the following query

SELECT `'1100 REG$'`.`Ap/ArID`, `'1100 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1100
REG$'` `'1100 REG$'`
UNION
SELECT `'1157 REG$'`.`Ap/ArID`, `'1157 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1157
REG$'` `'1157 REG$'`
UNION
SELECT `'1165 REG$'`.`Ap/ArID`, `'1165 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1165
REG$'` `'1165 REG$'`

The problem I am having is that I am getting a difference in the total
amount when the same query is generated individually for 1100 REG, 1157 REG
and 1165REG and aggregated. Why?

 

[Debra Dalgleish]

Try UNION ALL instead of UNION.

Hi

I am generating a pivot table using the following query

SELECT `'1100 REG$'`.`Ap/ArID`, `'1100 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1100
REG$'` `'1100 REG$'`
UNION
SELECT `'1157 REG$'`.`Ap/ArID`, `'1157 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1157
REG$'` `'1157 REG$'`
UNION
SELECT `'1165 REG$'`.`Ap/ArID`, `'1165 REG$'`.Amount
FROM `F:\Finance\Income Reconciliation\2004-05\GL transactions 0405`.`'1165
REG$'` `'1165 REG$'`

The problem I am having is that I am getting a difference in the total
amount when the same query is generated individually for 1100 REG, 1157 REG
and 1165REG and aggregated. Why?

 

[nc]

Hi Debra

Thanks, it works. What is the differene between the two?

 

[Debra Dalgleish]

By default, UNION excludes duplicate records. UNION ALL retains the
duplicate values.