Re: SQL count?

[CRPence <CRPbottle@xxxxxxxxx>]

David FOXWELL wrote:
> If I have this table <<SNIP; part of reply>>
>  
> how can I display the result :
>
> Client	count	count
> C1		3	2
>
> ie, the number of records where transaction type=A and
> the number of records where transaction type<>A

  The following ideas can be modified, possibly to more generically 
handle the request.  Any CTE or NTE could be a VIEW instead, which 
may be more worthwhile if the summation is typical; i.e. to avoid 
writing the query just for use in an ad hoc inquiry.  Alternative 
ideas from the typical "use CASE to count" implementations.

<code>

  create table ctt
  (client char(2) not null /* Client */
  ,trantp char    not null /* Transaction type */
  )
  ;
  insert into ctt values
   ('C1', 'A'),('C1', 'A'),('C1', 'B')
  ,('C1', 'C'),('C1', 'A')
  ;
  select 'C1' as "Client",A.*,B.* from
  from
   (select count(*) as "TranCt_A" from ctt
    where client='C1' and trantp='A'        ) A
  ,(select count(*) as "TranCt^A" from ctt
    where client='C1' and trantp<>'A'       ) B
  ;
    "Client"     "TranCt_A"      "TranCt_?"
       C1                 3               2
    ********  End of data  ********
  ;
  with
    sumA (client, trantp, tranct) as
    ( select client, trantp, count(*)
      from ctt
      where client = 'C1'
        and trantp = 'A'
      group by client, trantp
    )
   ,sumB (client,         tranct) as
    ( select client,         count(*)
      from ctt
      where client = 'C1'
        and trantp <> 'A'
      group by client
    )
      select a.client, a.tranct as "Trans A"
                     , b.tranct as "Trans Not A"
      from        sumA  a
       inner join sumB  b
      /* just as well cross join when one row each */
        on a.client=b.client
  ;
    CLIENT      "Trans A"   "Trans Not A"
      C1                3               2
    ********  End of data  ********

</code>

Regards, Chuck