Re: SQL count?

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