2 Replies Latest reply: May 16, 2009 12:52 PM by YVES BLAKE RSS

    How to do this query?

    mokano

      Hello,

      I have the follow situation:

      table: CUSTOMER
      customer_id | customer_name

      table: CAMPAING
      campaing_id | campaing_name

      table: PARTICIPATION
      campaing_id | customer_id

      Many customers can participate of many campaing, righ?

      I need a chart that returns:

      numofcampaings | qttyofusers
      1 | 15320 - number os persons that participated of just 1 campaing
      2 | 650 - number os persons that participated of 2 campaings
      3 | 120 - number os persons that participated of 3 campaings
      4 | 25 - number os persons that participated of 4 campaings

      Anyone can help me?

      thank you

       

       

       

        • How to do this query?
          Neil Miller

          SELECT customer_id, customer_name, Count(campaign_id) As campaign_count

          FROM customer, participation

          WHERE customer.customer_id = participation.customer_id

          GROUP BY customer_id, customer_name

          You could use that in place of loading the Customer table by itself. You would still probably want to load the Campaign and Participation tables as are. Then in your expressions, use Set Analysis to get the counts.

          Sum({<campaign_count = {1}>}, 1, 0) would be the number that participated in 1 campaign.

          The above query is Oracle SQL, here's T-SQL is you prefer:

          SELECT customer_id, customer_name, Count(campaign_id) As campaign_count

          FROM customer INNER JOIN participation ON customer.customer_id = participation.customer_id

          GROUP BY customer_id, customer_name

          I'd also expect that this could be done by simply loading your tables individually and then using expressions to do all the dirty work.

          • How to do this query?
            YVES BLAKE

            hello,

            the way to go is having a aggregated calculated dimension :

            aggr(count(campaign_id),customer_id)

            and display expression :

            count(distinct customer_id)