Re: Another SQL Question

[Dave Mahadevan <mahadevan@xxxxxxxx>]

Jromeh@aol.com wrote:
> 
> Dean asked...
> 
> <snip>
> >Is
> >there any way to incorporate the COUNT function with any other SQL
> >statements?  I usually get around this by running a query with detail and
> >another with COUNT.  What I need to do now is implement a GROUP BY with a
> >columnar SUM function, and include the COUNT with it.  Can this be done?
> > Seems that everything I try requires using the COUNT function by itself.
> 
> If you want both detail and summarization in the same statement, you can use
> UNION to create a single statement containing two SELECTs that both
> contribute to the same path. Of course, it performs best if the needed
> indices are present (typically those on the GROUPing fields, those used for
> selection). It looks like this:
> 
>  SELECT identifier_field, "D" AS rectyp, amount_field AS amount, 0 AS numrec
>  FROM   lib/file
>  WHERE  conditions
> UNION
>  SELECT identifier_field, "S", AS rectyp, sum(amount_field) AS amount,
> count(*)
>          AS numrec
>  FROM lib/file
>  WHERE (same) conditions
>  GROUP BY identifier_field
> ORDER BY identifier_field, rectyp

This is perfect and works great.  I learned something new in how to do
what QM & QRY/400 can do on the fly in SQL.  Thanks Jerry.

-- 
Thank You.

Regards

Dave Mahadevan.. mailto:mahadevan@fuse.net
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* This is the Midrange System Mailing List!  To submit a new message,   *
* send your mail to "MIDRANGE-L@midrange.com".  To unsubscribe from     *
* this list send email to MAJORDOMO@midrange.com and specify            *
* 'unsubscribe MIDRANGE-L' in the body of your message.  Questions      *
* should be directed to the list owner / operator: david@midrange.com   *
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *