General information
Category:
Coding, syntax & commands
>>>I am trying to build a view that totals an amount filed, I wish to have one column total be a YTD total and another column for just a particular day. I have tried using a join but that gives two rows for the result, I need one row with both totals.
>>>
>>>TIA
>>>
>>>Ian
>>
>>How about something like:
>>
>>SELECT cCommodity,sum(nAmount) as YTD, sum(iif(dDate=date(),nAmount,0)) as Today FROM YourTable GROUP BY 1
>>
>>HTH
>>Elyse
>Elyse,
>
>How about:
>
>nYear = YEAR(DATE())
>dToday = DATE()
>
>SELECT cCommodity, SUM(nAmount) AS YTD, 10000-10000 AS today FROM tTable WHERE YEAR(dDate)=nYear ;
> UNION ;
> (SELECT cCommodity, 0, SUM(nAmount) FROM tTable WHERE dDate=dToday) ;
> GROUP BY 1;
> INTO ...
>
>John.
And you will have 2 records for each Commodity, exactly what Ian tries to avoid! :)
Vlad
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only