Get counts for each unique fieldvalues from multiple tables.

[berryandcherry6]

Hi,

Table1:

Batch_id	Invitation_id	Comm_type_id
2	132	22
3	134	22
3	135	23
2	136	23
3	137	23
3	138	24

Table 2:

Batch_id	Invitation_id	Comm_type_id	User_signed
2	132	22	1
3	135	23	1
3	137	23	1
4	222	22	0
2	136	23	1
3	138	24	0
3	134	22	1

From above tables, i want to get all  Comm_type_id  from Table1, and match invitation_id, Batch_id in Table2 where User_signed = 1 for each comm_type_id, find sum and append it to Table1

I want to create table like this

Batch_id	Invitation_id	Comm_type_id	sum(user_signed)
2	132	22	1
3	134	22	1
3	135	23	2
2	136	23	1
3	137	23	2
3	138	24	0

How could i do this? Let me know if you need more info on this.

[kkkumar82]

Hi supriya,

I think , if you match both the tables with invitation_id, Batch_id  for each comm_type in the third table for the first row you have to get 1 but how you got 2 ? because between table 1 and table 2 you have only one record for 2, 132 combinations.

[berryandcherry6]

Hi,

Thanks

Yes, you are right. I have edited in query.

[balar025]

Is it irrespective of Invitation id?

because i can see to get sum(user_signed) you have used batch_id and comm_type_id.

is it right?

[kkkumar82]

Please close the thread if you feel you have got the answer.

[berryandcherry6]

No, both invitations in Table1 and Table 2 must match.

[balar025]

But same count you are showing for all invitation in same batch_id and comm_type_id. Is it right?

Like 3 and 23 combination.

[passionate]

Hi Supriya,

In your requirement for BatchId=3 and invitationId=135 how did you get Sum as 2?

I think something is missing.

Regards,

Pankaj

[balar025]

Please find attached application that might help you.

[berryandcherry6]

Hi,

for comm_type_id = 23, i have two rows which matches batch_id, inviation_id and comm_type_id in both the table where user_signed = 1

i.e 

2	136	23
3	137	23