× 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: Another SQL Question
  • From: Dave Mahadevan <mahadevan@xxxxxxxx>
  • Date: Fri, 18 Apr 1997 12:51:49 -0400
  • CC: Jromeh@xxxxxxx
  • Organization: Stoner and Associates

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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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.