star schema aggregation issue

Question

I have two dimension tables and one fact table as following:

drop table if exists ref;
create table ref (country_id int not null, id_ref int not null);

insert into ref values(1,1);
insert into ref values(1,2);

drop table if exists conv;
create table conv (country_id int not null, id_ref int not null,id_conv int not null,item varchar(25));

insert into conv values (1,1,1,'AA');
insert into conv values (1,2,2,'CC');
insert into conv values(1,2,3,'CA');
insert into conv values(1,2,4,'CA');

drop table if exists fact;
create table fact as
select 
r.country_id,c.item,
count(distinct r.id_ref) refs,
count(distinct c.id_conv) convs
 from ref r
left join conv c
on r.country_id=c.country_id
and r.id_ref=c.id_ref
group by 1,2;

Query to get results :

select f.country_id, sum(f.refs) refs,sum(f.convs) convs
from fact f
group by 1;

The result of above query is 1,3,4

but I am expecting 1,2,4

How can I achieve expected results or my concepts are wrong ?

Can you explain why you expect 1,2,4? Your fact table clearly has 3 results for refs, summing those would equal 3... Why do you need to group by item at all -- would this not work (it produces 1,2,4): http://sqlfiddle.com/#!2/22d3e8/3 — sgeddes, Dec 31 '14 at 22:06
because ref table have only two rows so if I count them i should get 2 as count of id_ref — Developer, Dec 31 '14 at 22:26

score 0 · Answer 1 · answered Dec 31 '14 at 22:12

I think you have an error in:

create table fact as
select 
r.country_id,c.item,
count(distinct r.id_ref) refs,
count(distinct c.id_conv) convs
 from ref r
left join conv c
on r.country_id=r.country_id
and r.id_ref=c.id_ref
group by 1,2;

Please try

left join conv c
    on r.country_id=c.country_id
    and r.id_ref=c.id_ref

instead of

left join conv c
    on r.country_id=r.country_id
    and r.id_ref=c.id_ref

(the following part looks like an error r.country_id=r.country_id - an always true expression)

score 0 · Answer 2 · answered Jan 06 '15 at 14:31

Your exception is wrong with this query. you are joining two table based on the country. They will be 4 matching record. after group by country &item , There will be three record.summarize distinct refid with item. actual result is correct.

country_id  item    refs    convs
1   AA  1   1
1   CA  1   2
1   CC  1   1

For your expectation ,query will be

select 
r.country_id,
count(distinct r.id_ref) refs,
count(distinct c.id_conv) convs 
 from ref r
left join conv c
on r.country_id=c.country_id
and r.id_ref=c.id_ref
group by  r.country_id

star schema aggregation issue

2 Answers2