>I'm trying to basically count data from 2 tables. I'm using a UNION command as such.
>
>sele cellcode,sum(IIF(betw(fdisp,"01","05"),1,0)) as sales,;
>sum(IIF(fdisp="01") as contacts,;
>sum(IIF(fdisp="01") as bad,;
>from table1;
>where curdate<="020505";
>group by cellcode;
>UNION;
>sele cellcode,sum(IIF(betw(fdisp,"01","05"),1,0)) as sales,;
>sum(IIF(fdisp="01") as contacts,;
>sum(IIF(fdisp="01") as bad,;
>from table2;
>where curdate<="020505";
>group by cellcode;
>
>Both tables are identical The problem is - I get duplicate cellcodes one below the other. Instead, I need the result summed up for both tables.
Ramone,
Union doesn't work the way you think and if it ever worked by coincidence you'd have your salees halved.
Table1.cellcode = 1
Table1.salestotal = 100
Table2.cellcode = 1
Table1.salestotal = 101
Would give 2 recs with same cellcode. If by coincidence salestotal was 100 in both you'd get one record with a salestotal of 100 (IOW it eliminates the dupes but doesn't sum).
You'd either do that in multipass SQL or with a single one - something like :
sele nvl(a.cellcode,b.cellcode) as cellcode, ;
sum(IIF(betw(nvl(a.fdisp," "),"01","05"),1,0)*nvl(a.sales,0)+;
IIF(betw(nvl(b.fdisp," "),"01","05"),1,0)*nvl(b.sales,0)) as sales ;
from table1 a ;
full join table2 b on a.cellcode = b.cellcode ;
where nvl(a.curdate,"9")<="020505" or nvl(b.curdate,"9")<="020505" ;
group by 1
Cetin