× 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: Jromeh@xxxxxxx
  • Date: Thu, 17 Apr 1997 08:52:31 -0400 (EDT)

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're using GROUP BY, adding a field defined as "COUNT(*)" will return
the number of records in each group. 

Otherwise, need to know more about where (in what SQL tool and for what
purpose) you're attempting to accomplish this. 

Are you attempting to retrieve the count for the group or for all records?

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

You can actually generate a file with level breaks in it if you follow this
path.

If you're trying to do the same thing going to screen or print with QM, you
can just generate the detail in the *QMQRY and tell the *QMFORM to do the
summarization for you.

HTH, 

Jerome Hughes  > these are my own opinions only

"Ask forgiveness, not permission."

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

Follow-Ups:

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.