Sum of distinc rows after a 1-many table join
- by Lock
I have 2 tables that I am joining.
Table 1 has 1-many relationship with table 2. That is, table 2 can return multiple rows for a single row of table 1. Because of this, the records of table 1 is duplicated for as many rows as are on table 2.. which is expected.
Now, I have a sum on one of the columns from table 1, but because of the multiple rows that get returned on the join, the sum is obviously multiplying.
Is there a way to get this number back to its original number? I tried dividing by the count of rows from table 2 but this didnt quite give me the expected result. Are there any analytical functions that could do this? I almost want something like
"if this row has not yet been counted in the sum, add it to the sum"
