aggregate count for particular field in table

[berryandcherry6]

Hi,

i have a table  FactTableDetail , here i need to aggregate  count of LandingHits and signedUser for ab_group_id in script . I built below code but it aggregate to campaign_id.

FactTable:

Load

`invitation_id`,

    `client_id`,

    `batch_meta_data_id`,

    `campaign_id`,

    `user_signed`,

`final_test_status`,

    `course_completed`,

`log_type_id`,

    date_accessed,

`batch_name`,

`campaign_name`,

`client_name`,

    `ab_group_id`

    Resident INVITATION ;

drop Table INVITATION;

FactTableDetail:

Load

`client_id`,

    `batch_meta_data_id`,

    `campaign_id`,

`batch_name`,

`campaign_name`,

`client_name`,

    `ab_group_id`,

     Count(distinct if(LEN(date_accessed)>0 , [invitation_id])) as LandingHits,

    Count(DISTINCT IF([user_signed]='1',[invitation_id])) as signedUser,

    Resident FactTable group by client_id, batch_meta_data_id, campaign_id, ab_group_id, `batch_name`,`campaign_name`,

`client_name`;

drop Table FactTable;

How could i do this? Please help me on this.

[jonathandienst]

This is one way:

FactTable:

Load

  invitation_id,

  client_id,

  batch_meta_data_id,

  campaign_id,

  user_signed,

  final_test_status,

  course_completed,

  log_type_id,

  date_accessed,

  batch_name,

  campaign_name,

  client_name,

  ab_group_id

Resident INVITATION ;

DROP Table INVITATION;

Join(FactTable):

Load

  ab_group_id,

  Count(DISTINCT If(LEN(date_accessed) > 0, invitation_id)) as LandingHits,

  Count(DISTINCT If(user_signed= '1', invitation_id)) as signedUser,

Resident FactTable

Group By ab_group_id;