Re: Another SQL Question

[Jromeh@xxxxxxx]

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