6 Replies Latest reply on Aug 4, 2006 2:41 PM by Newsgroup_User

    Avg and QoQ

    GeertS
      Hi all,
      Seems like I've got a problem with avg() and Query of Queries.
      I've got fields like this:
      100,null,100
      Now if I do an average with SQL server, it ignores the null. But this is not the case for QoQ. Is there another way to tell the avg that it needs to ignore empty lines?
      Thank you for your help
      Geert
        • 1. Re: Avg and QoQ
          Level 7
          GeertS wrote:
          > Hi all,
          > Seems like I've got a problem with avg() and Query of Queries.
          > I've got fields like this:
          > 100,null,100
          > Now if I do an average with SQL server, it ignores the null. But this is not
          > the case for QoQ. Is there another way to tell the avg that it needs to ignore
          > empty lines?
          > Thank you for your help
          > Geert
          >
          I'm not sure, never tested this, but since ColdFusion does not actually
          have a null, those may be empty strings in the QoQ. So maybe you can
          add a where clause to filter out the empty records

          WHERE averageField <> '' or something like that.
          • 2. Re: Avg and QoQ
            Dan Bracuk Level 5
            where averagefield * averagefield >= 0
            • 3. Re: Avg and QoQ
              GeertS Level 1
              Yes, but I have multiple avg(), want to show avg of sold price and offered price in one query. So that's not an option I'm affraid.
              Is there another possibility?
              Thank you
              Geert
              • 4. Re: Avg and QoQ
                Level 7
                Not that I can think of. Would it be completely horrible to use
                multiple QoQ's to do this, one for each field you want to average?

                GeertS wrote:
                > Yes, but I have multiple avg(), want to show avg of sold price and offered price in one query. So that's not an option I'm affraid.
                > Is there another possibility?
                > Thank you
                > Geert
                >
                • 5. Re: Avg and QoQ
                  GeertS Level 1
                  To clarify
                  Field 1 Field 2
                  100 200
                  null 100
                  100 300
                  Do not want to delete line two completely.
                  Geert
                  • 6. Re: Avg and QoQ
                    Level 7
                    GeertS wrote:
                    > To clarify
                    > Field 1 Field 2
                    > 100 200
                    > null 100
                    > 100 300
                    > Do not want to delete line two completely.
                    > Geert

                    And if you willing to put up with the extra overhead, this could be done
                    pretty simply, albeit redundantly, with two QoQ blocks.

                    <cfquery name="fieldOneAvg" dbtype="query">
                    SELECT AVG(Field1) AS Average
                    FROM RecordSetVar
                    WHERE Field1 <> ''
                    </cfquery>

                    <cfquery name="fieldTwoAvg" dbtype="query">
                    SELECT AVG(Field2) AS Average
                    FROM RecordSetVar
                    WHERE Field2 <> ''
                    </cfquery>

                    <cfoutput>
                    #fieldOneAvg.Average#<br/>
                    #fieldTwoAvg.Average#
                    </cfoutput>

                    With a touch extra effort, once could probable create a UDF or CustomTag
                    that would allow for one reusable QoQ to be passed parameters and create
                    all the averages.