Re: Count number of hits.

"Dmitry E Loginov" <lde_at_mpsb.ru> wrote in message news:<as02rf$tc5$1_at_news.caravan.ru>...
> "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

Thanks Dmitry, it works great :o)

/Kenneth Received on Wed Nov 27 2002 - 02:34:57 CST