Re: SQL COUNT - detail record count on header records

[MacWheel99@xxxxxxx]

AS400 Network has an on-line forum just for SQL & DB & Query type Q+A.
http://www.as400network.com/Forums/Main.cfm?CFApp=59

Use the search engine ... you may even find where I had a very similar 
question answered a few months ago.  There are a lot of threads how to do 
this & that function.

I do not have the precise code in front of my memory since I do my e-mail 
from home but I believe it is something like COUNT(*) SUM(*) where you select 
what field is to be totalled out of which records to be included.  I do have 
this in a program at the office ... SYFILCOUNT which is using an *OUTFILE of 
a file list to get # records / file then SQL SELECT is getting how many in 
various categories.

> From: Patrick.Archibald@HOMETELCO.COM (Patrick L Archibald)

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


MacWheel99@aol.com (Alister Wm Macintyre) (Al Mac)
AS/400 Data Manager & Programmer for BPCS 405 CD Rel-02 mixed mode (twinax 
interactive & batch) @ http://www.cen-elec.com Central Industries of 
Indiana--->Quality manufacturer of wire harnesses and electrical 
sub-assemblies - fax # 812-424-6838

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