[RBG7-L] - Re: SQL select question

[Lawrence Lustig]

> Is there a way to get the output to be on 1 line per Tr_date?
> I want the output to look like this without using a view?
> 
>   select tr_date,count(distinct custnum),$0.00,$0.00,$0.00,$0.00,$0.00 +
>    from tran_hist where tr_type = 1  GROUP BY tr_date
>   union +
>   SELECT tr_date,0,SUM(ch_price)$0.00,$0.00,$0.00,$0.00 FROM tran_hist +
>   WHERE tr_type = 1  GROUP BY tr_date +
>   UNION +
>   SELECT tr_date,0,$0.00,$0.00,SUM(ch_price)=s,$0.00,$0.00 FROM tran_hist +
>   WHERE ch_code = 'ipay'  GROUP BY tr_date  several more Unions ......

Try this:

SELECT TR_Date, +
  SUM(IFEQ(TR_Type, 1, 1, 0)), +
  SUM(IFEQ(TR_Type, 1, Ch_Price, 0)), +
  SUM(IFEQ(CH_Code, 'ipay', Ch_Price, 0)), +
  . . . More SUMs Here . . . +
  FROM Tran_Hist GROUP BY TR_Date

--
Larry