Wrong answer after aggregating 1

[Jony77]

Hello,

I am getting a wrong answer (lesser amount) after aggregation. The sum(quantity) in prod_fact_table is 2,089,033 but if I run the insert statement below the sum(quantity) in agg01_agg01_prod_fact_table will be 1,689,785 which is less. The two must be the same. Please what can be the problem? Any idea? See the insert statement below:

INSERT INTO agg01_prod_fact_table(
fact_count,
quantity,
machine_group_key,
location_key,
year,
quarter,
month,
day_of_month,
hour
)
SELECT
COUNT(*) AS fact_count,
SUM(BASE.quantity),
BASE.machine_group_key,
BASE.location_key,
DIM.year,
DIM.quarter,
DIM.month,
DIM.day_of_month,
DIM.hour
FROM prod_fact_table AS BASE, times AS DIM
WHERE DIM.times_key = BASE.times_key
GROUP BY
BASE.machine_group_key,
BASE.location_key,
DIM.year,
DIM.quarter,
DIM.month,
DIM.day_of_month,
DIM.hour;

Thanks,

 

[blom0344]

The obvious cause is , that by using an inner join, you are losing records from the facttable cause some times_key have no matching record from the BASE table.

Run the SQL using an outer join, like:

INSERT INTO agg01_prod_fact_table(
fact_count,
quantity,
machine_group_key,
location_key,
year,
quarter,
month,
day_of_month,
hour
)
SELECT
COUNT(*) AS fact_count,
SUM(BASE.quantity),
BASE.machine_group_key,
BASE.location_key,
DIM.year,
DIM.quarter,
DIM.month,
DIM.day_of_month,
DIM.hour
FROM prod_fact_table AS BASE left outer join times AS DIM
on BASE.times_key = DIM.times_key
GROUP BY
BASE.machine_group_key,
BASE.location_key,
DIM.year,
DIM.quarter,
DIM.month,
DIM.day_of_month,
DIM.hour;

Ties Blom