RE: SQL COUNT - detail record count on header records

["MCPARTLAND, Stan" <stanley.mcpartland@xxxxxxxxxx>]

Patrick,

By default you have an inner join which doesn't present a row when there
isn't an entry in the joined table.  Recode your SELECT statement as a LEFT
OUTER JOIN.  I prefer to code joins explicitly instead of using the default
inner join even when an inner join is required.  I haven't tested the
example below, but it should get you started.

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

Regards,
Stan McPartland

-----Original Message-----
From: Patrick L Archibald [mailto:Patrick.Archibald@HOMETELCO.COM]
Sent: Tuesday, 10 April, 2001 10:45 AM
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
+---