× 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.


  • Subject: RE: SQL COUNT - detail record count on header records
  • From: "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>
  • Date: Tue, 10 Apr 2001 14:19:27 -0500

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 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.