I'm having a view that's actually a combination of 2 other views, which in they turn are the split of Table1 join Table2. The split in the 2 view is based on whether the column of the table1 EnterDate=curDate(), which determines the way a new column is calculated ( NewColumn=(Table2.Cpx-Table1.Px)*Table1.Size if EnterDate=curdate() and
NewColumn=(Table2.Cpx-Table2.YPx)*Table1.Size if EnterDate<>curdate(), Table1 join Table2 on Table1.Name=Table2.Name * )
The Last View is:
Table1.Name, SUM(Table1.Size), Table2.Cpx, format(sum(coalesce(View1.NewColumn,'0') + View2.NewColumn),2)
from (((Table1 join Table2 on(Table1.Name = Table2.Name)) left join View1 on(View1.Name = Table1.Name)) left join View2 on(View2.Name = Table1.Name))) group by Table1.Name order by Table1.Name
In order to tidy up the whole thing and minimize the time it takes to show the data I tried the following:
Table1.Name , Table2.Cpx , sum(Table1.Size) , format(sum(((Table1.Cpx - if((Table1.EnterDate = curdate()), Table1.Px , Table2.Ypx)) * Table1.Size)),2) from (Table1 join Table2 on ((Table1.Name = Table2.Name))) group by Table1.Name order by Table1.Name
The problem is it doesn't show the Sum of the Size or the sum of the New Column, it only takes the first one of each different Name. Am I doing something wrong, or is there another way to do this?
*NOTE in Table1 the 'Name' is the same for more than 1 Row