0 Replies Latest reply: Aug 8, 2017 10:26 AM by Supriya R RSS

    Loop through records and form table with sums

    Supriya R

      Hi,

       

      i have a very very large records table, (for example for ab_test_id = 23, it contain upto 20,000 records)

       

      Table1:

      iduser_idbatch_idinvitation idab_test_idemail_id
      11223dsadad21231
      21324assasd23232
      31426dsdsad2231
      41423dasdsd23261
      51225sdad23262
      61723sdad231282

       

      Now i have one more table like below

       

      Table 2:

      batch_idemail_idinvitation iduser_signed
      231dsadad211
      242assasd230
      261dsdsad21
      231dasdsd230

       

       

      Now i want to create a table with  id, batch_id, user_id, invitation_id, ab_test_id, email_id along with below

      1.)count(invitation id) as HITs, where Table1.email_id = Table2.email_id and Table1.batch_id = Table2.batch_id  and Table1.invitation id = Table2.invitation id for each email_id

      2.)count(invitation id) as signed, where Table1.email_id = Table2.email_id and Table1.batch_id = Table2.batch_id and and Table1.invitation id = Table2.invitation id  and user_signed = 1 for each email_id.


      To create like this kind, if i use join it will take very very long time to load, so what could be done?


      Let me know if you need more info on this. Please help me on this.