Re: SQL CASE Question

[Jeff Young <jyoung0950@xxxxxxxxx>]

Thanks Charles.
This will work.
I was not clear in my original post, but I will be using this in an RPGLE
program.




On Tue, Apr 24, 2012 at 10:34 AM, Charles Wilt <charles.wilt@xxxxxxxxx>wrote:

> Missed you wanting the count...
>
> easy enough to add...
>
> select
>  sum(case when type = 'IN' then Amt else 0 end) as Total_IN_Amt,
>  sum(case when type = 'IN' then 1 else 0 end) as Total_IN_Cnt,
>  sum(case when type = 'IN' then Amt else 0 end) as Total_IN_Amt,
>  sum(case when type = 'IN' then 1 else 0 end) as Total_IN_Cnt,
> <...>
>
> Charles
>
> On Tue, Apr 24, 2012 at 10:32 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
> wrote:
> > You want to pivot the data in other words...
> >
> > select
> >  sum(case when type = 'IN' then Amt else 0 end) as Total_IN,
> >  sum(case when type = 'CM' then Amt else 0 end) as Total_CM,
> > <...>
> >
> > Charles
> >
> >
> > On Tue, Apr 24, 2012 at 10:24 AM, Jeff Young <jyoung0950@xxxxxxxxx>
> wrote:
> > > All,
> > > Is it possible within the WHEN clause of the CASE statement to have
> > > multiple operations?
> > > I have a condition where I need to get both the count and total amount
> for
> > > selected transaction types into separate variables by transaction type.
> > > I do not want to use a cursor to summarize by type.
> > >
> > > Transaction File
> > > Type   2A
> > > Amt   11  2
> > >
> > > Type can be 'IN', 'CM,',PA','DM'
> > >
> > > I want to get the total transactions and amount by type.
> > >
> > > Thanks,
> > >
> > > --
> > > Jeff Young
> > > Sr. Programmer Analyst
> > > --
> > > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> > > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > > To subscribe, unsubscribe, or change list options,
> > > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > > Before posting, please take a moment to review the archives
> > > at http://archive.midrange.com/midrange-l.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.