RE: SQL count?

["Dan Kimmel" <dkimmel@xxxxxxxxxxxxxxx>]

This one has the advantage of producing a record for each client.

select client, count(case when ttype='A'then '1' else null end ) as
"Type =
A", count(case when ttype<>'A' then '1' else null end ) as "Type<>A"   
 from danlib.test group by client

It uses the notion that the aggregate function "count" only increments
when the expression is not null.
                                        

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Thursday, March 11, 2010 3:50 AM
To: Midrange Systems Technical Discussion
Subject: SQL count?

Hi all,

If I have this table,

client transaction type
C1		A
C1		A
C1		B
C1		C
C1		A    
 
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  


Thanks,
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.