Key Ken kay Key Ken kay - 15 days ago 8
SQL Question

Sum data in two tables and insert it in another one without duplucate ID

I have 3 tables..


House1
House2
results


house1
ID, Name, Monday, Tuesday
1 john 1 1
2 jack 1 0


and

House2
ID, Name, Monday, Tuesday
3 Dan 0 0
1 John 1 0


and I want to fill the
results
table, something like this:

results
ID, Name, Total
1 john 3
2 jack 1
3 dan 0


im using the IIF() to count the days.. but it made dupicate rows

im using something similar to:

INSERT INTO results (ID, name, total)
SELECT ID, name, IIf([house1.monday]>0,1,0)+
IIf([house2.monday]>0,1,0)+
IIf([house1.tuesday]>0,1,0)+
IIF([house2.tuesday]>0,1,0) as TOTAL
FROM house1,house2
WHERE House1.ID = House2.ID


that clearly doesn't work, because it only insert the data of
john
.

Answer

You recognize the problem with your query. The inner join only keeps matching rows.

You can keep all rows by using union all instead. The following calculates the total for each table and then uses aggregation to sum them:

INSERT INTO results(ID, name, total)
    SELECT ID, name, SUM(Total) as TOTAL
    FROM ((select h1.id, IIf([h1.monday]>0,1,0) + IIf([h1.tuesday]>0,1,0) as Total
           from house1 h1
          ) union all
          (select h2.id, IIf([h2.monday]>0,1,0) + IIf([h2.tuesday]>0,1,0) as Total
           from house2 h2
          )
         ) h
    group by id, name;

EDIT

You changed the question in your comment. However, you would just do the same thing, defining the columns that you need in the subquery:

INSERT INTO results(ID, name, totalMonday, totalTuesday, total)
    SELECT ID, name, SUM(Monday), SUM(Tuesday), SUM(Monday)+Sum(Tuesday) as TOTAL
    FROM ((select h1.id, IIf([h1.monday]>0,1,0) as Monday, IIf([h1.tuesday]>0,1,0) as Tuesday
           from house1 h1
          ) union all
          (select h2.id, IIf([h2.monday]>0,1,0) as Monday, IIf([h2.tuesday]>0,1,0) as Tuesday
           from house2 h2
          )
         ) h
    group by id, name;