6 Replies Latest reply on May 22, 2007 7:55 AM by Newsgroup_User

    QofQ error

    JohnGree Level 1
      Hi i am getting a error of

      Error Executing Database Query.

      Query Of Queries runtime error.
      The aggregate function [SUM(expression)] cannot operate on an operand of type [VARCHAR].

      i have 2 columns in mysql database which are both DECIMAL fromats

      i need to have a QofQ which i need but not sure how to change the queries to stop the error

      <cfquery name="GetRecords" datasource="#application.ds#">
      Select DateOfSM, RCODE, DAY(DateOfSM)as month3, OurCost, ClientCostPerSMS
      FROM sms_records
      WHERE MONTHNAME(DateOfSM)='#session.Daily#' AND YEAR(DateOfSM)=#session.DayYear#
      </cfquery>

      <cfquery name="GetRecords2" dbtype="query">
      Select sum(OurCost)as TotalIncome, sum(ClientCostPerSMS)as Totalcost, month3
      FROM GetRecords
      GROUP BY month3
      </cfquery>
        • 1. Re: QofQ error
          Level 7
          Which version of CF are you running? If 7+, try using the CAST() function
          to force QoQ to understand the numeric cols are actually numeric. I'm not
          sure why you'd need to do this, but QoQ works in mysterious ways,
          sometimes.

          --
          Adam
          • 2. Re: QofQ error
            JohnGree Level 1
            how do i use the cast function to do this?
            • 3. Re: QofQ error
              Level 7
              > how do i use the cast function to do this?

              Did you look up the documentation for it? There's not a great deal to it.

              --
              Adam
              • 4. Re: QofQ error
                JohnGree Level 1
                ok found it thanks

                but i am getting a weird result

                sum(cast(ClientCostPerSMS as INTEGER))as AmountWeCharged

                this column has decimals as 0.10 and 0.08 etc nothing over the amount of 1

                the result i am getting is 0 but this is not true. how can i do a sum of a decimal?

                thanks
                • 5. Re: QofQ error
                  Level 7
                  Have you tried casting to numeric or decimal?

                  --
                  Bryan Ashcraft (remove brain to reply)
                  Web Application Developer
                  Wright Medical Technology, Inc.
                  ------------------------------------------------------------------
                  Macromedia Certified Dreamweaver Developer
                  Adobe Community Expert (DW) :: http://www.adobe.com/communities/experts/



                  "JohnGree" <webforumsuser@macromedia.com> wrote in message
                  news:f2urog$4v0$1@forums.macromedia.com...
                  > ok found it thanks
                  >
                  > but i am getting a weird result
                  >
                  > sum(cast(ClientCostPerSMS as INTEGER))as AmountWeCharged
                  >
                  > this column has decimals as 0.10 and 0.08 etc nothing over the amount of
                  > 1
                  >
                  > the result i am getting is 0 but this is not true. how can i do a sum of a
                  > decimal?
                  >
                  > thanks
                  >
                  >


                  • 6. Re: QofQ error
                    Level 7
                    >> ok found it thanks

                    Sorry to seem a bit obtuse before. I figure it's more help to people to
                    kind of encourage them to help themselves, rather than just dish out
                    answers. It can be annoying at times, I know.


                    >> sum(cast(ClientCostPerSMS as INTEGER))as AmountWeCharged
                    >> this column has decimals as 0.10 and 0.08 etc nothing over the amount of 1
                    >> the result i am getting is 0

                    > Have you tried casting to numeric or decimal?

                    Good advice.

                    http://en.wikipedia.org/wiki/Integer

                    In casting it as an integer, you're ASKING it to "lop off" the decimal
                    fraction part of all the numbers. Leaving... zero. For all of them.

                    --
                    Adam