|
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 +---
As an Amazon Associate we earn from qualifying purchases.
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.