RE: SQL COUNT - detail record count on header records

Patrick,

Hmmmm. Off the top of my head (not tested), I'd go with:

SELECT a.phonenum, a.ordrnmbr, COALESCE(count(b.*),0),
COALESCE(sum(b.amountfield),0) 
 FROM som.som022clsd a 
  LEFT OUTER JOIN som.sompfa21 b 
  ON (a.phonenum = b.phonenum 
      and a.ordrnmbr = b.ordrnmbr)
 WHERE a.conumber = '08' and a.workdate between 20010301 and 20010330
 GROUP BY a.phonenum, a.ordrnmbr


The reason I think this will work for you is the nature of null records and
the left outer join processing. Null records will be returned with the join
if no detail records match the header join criteria. Since null records
aren't used by the aggregate functions count or sum, you end up with the
values you need. I went back and added the coalesce because I think its is
needed to return zeros you are looking for.

I probably muddled this, but give it a try and let me know.

hth,
Eric DeLong

-----Original Message-----
From: Patrick L Archibald [mailto:Patrick.Archibald@HOMETELCO.COM]
Sent: Tuesday, April 10, 2001 12:45 PM
To: MIDRANGE-L@midrange.com
Subject: SQL COUNT - detail record count on header records


Hi

I've racked my brains and searched high and low.
I can't find the correct SQL SELECT statement to 
count the number of records in a detail file and 
show that count in my header record. 

The following SELECT statement gives me what I want
except I want to see zero in the count field if 
there are no matching records in the detail file. 

select a.*, b.count from som.som022clsd as a, (select phonenum,        
ordrnmbr, count(*) as count from som.sompfa21 group by phonenum,       
ordrnmbr) as b where a.phonenum = b.phonenum and a.ordrnmbr =      
b.ordrnmbr and a.conumber = '08' and a.workdate between 20010301   
and 20013030                                                       

Previously I wanted to sum a total in a detail file and show 
it along the header fields but couldn't figure that out either. 
Again I want to show zero in the sum field if there are no 
matching records in the detail file. 

Can someone please point me in the right direction?

Thanx, PLA
/ 
/ Patrick L Archibald
/ http://HomeTelco.com/pla/
/
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---