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
Thanks for the help. the following did the trick:
SELECT Daily.group, Daily.code, ;
SUM(iif(daily.date < ?which_date AND daily.sh_re = "R",Daily.net,0)) as YTD_WET, ;
SUM(iif(daily.date < ?which_date AND sh_re = "R",Daily.dry,0)) as YTD_DRY, ;
SUM(iif(daily.date < ?which_date AND sh_re = "S",Daily.net,0)) as YTD_S, ;
SUM(iif(daily.date = ?which_date AND sh_re = "R",Daily.net,0)) as DAY_WET, ;
SUM(iif(daily.date = ?which_date AND sh_re = "R",Daily.dry,0)) as DAY_DRY, ;
SUM(iif(daily.date = ?which_date AND sh_re = "S",Daily.net,0)) as DAY_S ;
FROM wits!daily ;
GROUP BY Daily.group, Daily.code ;
Thanks also to all the others who offered solutions.
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