> for select
> iif(n.code=1,sum(n.amount),0),
> iif(n.code=2,sum(n.amount),0),
> iif(n.code=3,sum(n.amount),0),
> n.sell_date
> from new_table n
> where n.sell_date between :fromdate and :uptodate
> group by n.sell_date,n.code
> into
> :stock,
> :repair,
> :sh,
> :sell_date
>
>This gives the correct totals but not in the correct format:
>
>SELL_DATE STOCK REPAIR SH
>07.11.2014 0 0 30
>10.11.2014 160 0 0
>10.11.2014 0 20 0
>11.11.2014 100 0 0
>11.11.2014 0 2200 0
>11.11.2014 0 0 500
>
>What I want is this format, one row per day:
>
>SELL_DATE STOCK REPAIR SH
>07.11.2014 0 0 30
>10.11.2014 160 20 0
>11.11.2014 100 2200 500
You almost got it right, Alan, just remember to only group only on things you
want to produce a separate row. This is the query you want:
select sell_date,
sum(iif(code=1, amount,0)) stock,
sum(iif(code=2, amount,0)) repair,
sum(iif(code=3, amount,0)) SH,
from new_table
where n.sell_date between :fromdate and :uptodate
group by sell_date
Set
