"Kenneth Osenbroch" <kenneth.osenbroch_at_canaldigital.com> wrote in message
news:9c941a28.0211242325.53fc0680_at_posting.google.com...
> Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message
news:<3DDAD17F.6BA6E4B9_at_exesolutions.com>...
> > Kenneth Osenbroch wrote:
> >
> > > Hi all.
> > >
> > > I want to show the number of hits of a certain status from a table.
> > > The result should be something like this:
> > >
> > > Custnr StatusA StatusB StatusC
> > > 123456 1 0 0
> > > 123457 0 0 0
> > > 123458 3 1 0
> > > 123459 9 2 2
> > > .
> > > .
> > >
> > > Can anybody help me with this? Any help would be greatly appreciated
> > > :o)
> > >
> > > /Kenneth
> >
> > Show us your best attempt and we will give you hints.
> >
> > Daniel Morgan
>
> Hi again, and thanks for your reply.
>
> By using the following query, I manage to list and count all statuses I
want.
>
> SELECT INVSUBSNR, INVINVOICESTATUS, COUNT(1)
> FROM
> INVOICES
> WHERE
> (INVINVOICESTATUS IN ('R' ,'1' ,'2' ,'5' ,'6' ,'7') ) OR
> (INVINVOICESTATUS IN ('W' ,'4' ) ) OR
> (INVINVOICESTATUS IN ('C' ) )
> GROUP BY INVSUBSNR, INVINVOICESTATUS;
SELECT INVSUBSNR
, sum(sign(Instr('R12567', nvl(INVINVOICESTATUS,'x')))) statusA
, sum(sign(Instr('W4', nvl(INVINVOICESTATUS,'x')))) statusB
, sum(sign(Instr('C', nvl(INVINVOICESTATUS,'x')))) statusC
FROM
INVOICES
WHERE
(INVINVOICESTATUS IN ('R' ,'1' ,'2' ,'5' ,'6' ,'7') ) OR
(INVINVOICESTATUS IN ('W' ,'4' ) ) OR
(INVINVOICESTATUS IN ('C' ) )
GROUP BY INVSUBSNR
>
> Any hints on how I can get the result out in mentioned manner?
>
> /Kenneth
Received on Tue Nov 26 2002 - 09:07:55 CST
