RE: SQL question with subselect

That would work but I'd rather see

select vendor, vndname, vndad1, vndad2, vndad3,
  vstate, vpost, vdtlpd, count(abvnd)
from
  bpcsf/avm join bpcsf/abr
                   on vendor = abvnd
where
  absts <> 'W'
group by vendor, vndname, vndad1, vndad2, vndad3,
  vstate, vpost, vdtlpd
order by vendor

or using a more readable more on the inner join


select vendor, vndname, vndad1, vndad2, vndad3,
  vstate, vpost, vdtlpd, count(abvnd)
from
  bpcsf/avm, bpcsf/abr
where
  vendor = abvnd
  and absts <> 'W'
group by vendor, vndname, vndad1, vndad2, vndad3,
  vstate, vpost, vdtlpd
order by vendor


Since there is only be one record for each vendor, you can group by all
fields selected from the vendor master (AVM) without affecting the query.
Using the MAX function, while returning the same results would imply that
there are or at least could be multiple records for each vendor in the
vendor master file.

HTH,
Charles
  

> -----Original Message-----
> From: rick.baird@xxxxxxxxxxxxxxx [mailto:rick.baird@xxxxxxxxxxxxxxx]
> Sent: Friday, February 13, 2004 4:35 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: SQL question with subselect
> 
> 
> 
> I should read the entire message before answering :(
> 
> >In a group by situation in a sub query, it seems that I can 
> have only one
> >column selected.  Obviously without the count by vendor, the 
> report is
> >useless so how do I get that count & vendor number?
> 
> something like this:
> 
> select distinct vendor,
>     max(vndnam), max(vndad1),
>     max(vndad2), max(vndad3),
>     max(vstate), max(vpost),
>     max(vdtlpd), count(abvnd)
> from bpcsf/avm
> join bpcsf/abr
> on vendor = abvnd
> group by abvnd
> 
> 
> _______________________________________________
> This is the Midrange Systems Technical Discussion 
> (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>