× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.