data model suggestion

[Krish2459_58]

Hi, 

I have two tables Lease and Unit where one lease can have multiple Units.

I have to bring unit information into the lease table whic causes the sum(area) mutilplied like below.

Attached the sample data.Please have acheck.

[Gaël]

Based on your very short description, I guess Sum(DISTINCT Area) is what you need (assuming no two areas are equal).

[marcus_sommer · Accepted solution]

Both tables associated per Lease ref should return the correct sum() per row as well as in the total respectively only on the Lease ref level. Displaying it on an unit ref level has no added value else only potential to confuse the users.

If there should be for other reasons a join of the tables needed you may add an extra information of how many units a lease has, something like:

join(Lease) load LeaseRef, count(UnitRef) as Count resident Unit group by LeaseRef;

and within the UI an expression may look like:

sum(Area / Count)