Tim,
Will this work for you?
Sum(IIF(Customerlineitem.packageindicatorcolortypecode="COLOR",1,0)) As ColorType;
>
>I have an SQL select statement where I am joining two tables. I want to be able to count for a field equaling a certain value. I've tried many variations of the COUNT and SUM functions in my SQL statement but I haven't got it to work. here is my SQL select statement:
>
>
>SELECT customerorder.jobnumbercode , Sum(Customerlineitem.packageindicatorquantity) AS PkgCount,;
>Sum(Customerlineitem.packageindicatorretouchflag) AS Retouch,;
>Sum(Customerlineitem.packageindicatorsoftfocusflag) AS SoftFocus,;
>Sum(Customerlineitem.packageindicatoreffectflag) AS Effects,;
>**Sum(Customerlineitem.packageindicatorcolortypecode="COLOR") As ColorType;
>FROM customerorder ;
>INNER JOIN customerlineitem ;
>ON Customerorder.orderkey = Customerlineitem.orderkey;
>WHERE Customerorder.ordercanceledflag = 0;
>GROUP BY customerorder.jobnumbercode
>
>
>I've indicated what line doesn't work (**). IF anyone has any ideas on how to fix this, I would very much appreciate it.
>
>Thanks!
>
>
--sb--