37 Replies Latest reply on May 3, 2007 1:19 PM by BKBK

    query sum issue

    JohnGree Level 1
      Hi i have this query below but on my <CFDUMP VAR=#getUser#> i get massive numbers for SMSBought, any ideas what i need to do?


      <CFQUERY datasource="#application.ds#" Name="GetUser">
      SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password,
      SUM(SR.Cost) AS SMSCost, SUM(PTS.SMS_Amount) AS SMSBought
      FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS


      WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
      GROUP BY PTS.ClubID, CT.ClubID, SR.ClubID
      </cfquery>
        • 1. Re: query sum issue
          Dan Bracuk Level 5
          What does this give you?
          select max(sms_amount)
          FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS


          WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
          • 2. query sum issue
            paross1 Level 2
            Shouldn't your group by actually look like this instead?

            GROUP BY CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password

            PTS.ClubID, CT.ClubID, and SR.ClubID would all be the same value and you are actually only selecting CT.ClubID in your SELECT.

            Phil
            • 3. Re: query sum issue
              JohnGree Level 1
              Hi i have tried the group clause and still i get the same result

              the max output was 2375

              i also did this

              select SUM(PTS.SMS_Amount) AS SMSBought
              from payment_table PTS
              group by clubid

              which shows the results fine, so the problem comes when i add the two tables SMS_Clubs_Table and SMS_Records

              any ideas why this would be hapening
              • 4. Re: query sum issue
                Dan Bracuk Level 5
                I have an idea why it would be happening.

                to make a long story short, add this to your where clause
                and sr.clubID = sms.clubID
                • 5. Re: query sum issue
                  BKBK Adobe Community Professional & MVP
                  GROUP BY PTS.ClubID

                  • 6. Re: query sum issue
                    JohnGree Level 1
                    ok i have tried that but still the same, also what is sms.clubid?
                    • 7. Re: query sum issue
                      BKBK Adobe Community Professional & MVP
                      What do these give us?

                      <CFQUERY datasource="#application.ds#" Name="GetUser">
                      SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password, SUM(PTS.SMS_Amount) AS SMSBought
                      FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS
                      WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
                      GROUP BY PTS.ClubID
                      </cfquery>

                      <CFQUERY datasource="#application.ds#" Name="GetUser">
                      SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password, SUM(PTS.SMS_Amount) AS SMSBought, SUM(SR.Cost) AS SMSCost
                      FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS
                      WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
                      GROUP BY PTS.ClubID
                      </cfquery>



                      • 8. Re: query sum issue
                        BKBK Adobe Community Professional & MVP
                        P.S.: I wondered whether the group by was having more of an effect on SUM(SR.Cost) instead

                        • 9. Re: query sum issue
                          JohnGree Level 1
                          no that still gives the same bogus numbers?

                          i dont understand, when i do the query on it own i get the correct results
                          • 10. Re: query sum issue
                            BKBK Adobe Community Professional & MVP
                            that still gives the same bogus numbers?
                            Even the first query? OK, just to corner the gremlin, what about

                            <CFQUERY datasource="#application.ds#" Name="GetUser">
                            SELECT SUM(PTS.SMS_Amount) AS SMSBought
                            FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS
                            WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID
                            GROUP BY PTS.ClubID
                            </cfquery>
                            • 11. query sum issue
                              efecto747 Level 1
                              Your query is creating two inner joins on the SMS_Clubs_Table to two tables which have a many to one relationship with it. This will give you more rows than you're expecting as it returns a row for each matching record in SMS_Records and each matching record in payment_table - this is why your totals are out.

                              For example, if a record in SMS_Clubs_Table had 3 related records in the SMS_Records table and 3 related records in payment_table then you'd get 9 rows back. Any aggregate functions - SUM() COUNT() etc. - would be incorrect.

                              Try breaking it into two queries or make it into a UNION query.

                              cheers.
                              • 12. Re: query sum issue
                                BKBK Adobe Community Professional & MVP
                                Efecto747
                                > For example, if a record in SMS_Clubs_Table had 3 related records in the
                                > SMS_Records table and 3 related records in payment_table then you'd get
                                > 9 rows back. Any aggregate functions - SUM() COUNT() etc. - would be incorrect.


                                My thoughts, too. For example, clubID is possibly not a unique foreign key in one or more of the tables.

                                My last query is meant to show that. It is the same as the query that returns the expected answer, with the exception of the join statements.

                                • 13. Re: query sum issue
                                  BKBK Adobe Community Professional & MVP
                                  A quick test to see what's going on. Create the 3 tables t1, t2, t3. Each has 2 columns. One column is id, of type int, the other is txt, of type varchar.

                                  Insert the following 6 rows:

                                  table t1
                                  =======
                                  id=1, txt =x

                                  table t2
                                  =======
                                  id=1, txt=y1
                                  id=1, txt=y2

                                  table t3
                                  =======
                                  id=1, txt=z1
                                  id=1, txt=z2
                                  id=1, txt=z3

                                  Run the code

                                  <cfquery name="q" datasource="dsn">
                                  select sum(t1.id) as s
                                  from t1, t2, t3
                                  where t1.id=t2.id and t1.id=t3.id and t2.id=t3.id
                                  group by t1.id
                                  </cfquery>
                                  <cfdump var="#q#">

                                  The value of s will be 6, not 1.

                                  • 14. Re: query sum issue
                                    JohnGree Level 1
                                    ok yes i understand what the problem is, how would i create the query using unions?

                                    would applying a union stop the problem? if so how would i use unions in my query?
                                    • 15. Re: query sum issue
                                      BKBK Adobe Community Professional & MVP
                                      In my opinion, doing a union will take you away from your original logic, that of joins. A union requires that the concatenated select-statements have the same structure. They must have the same number of columns. Corresponding columns must have the same data type or data types that can be converted to each other. The order of the columns in each SELECT statement matters, too. The result will unlikely be what you wanted to get with joins.

                                      What comes to mind is that you first leave out the aggregates, because the ones you get with a join are different from the ones you need. Get the other information you need

                                      SELECT CT.ClubName, CT.ClubID, CT.ContactEmail, CT.Password
                                      FROM SMS_Clubs_Table CT, SMS_Records SR, payment_table PTS
                                      WHERE CT.ClubID = SR.ClubID AND CT.School = 0 AND CT.ClubID = PTS.ClubID


                                      and get your aggregates as before

                                      select SUM(PTS.SMS_Amount) AS SMSBought
                                      from payment_table PTS
                                      group by clubid

                                      select SUM(SR.Cost) AS SMSCost
                                      from SMS_Records SR
                                      group by clubid


                                      It's all up to you. If you're not satisfied with the information as it stands, you can then proceed with a union or with a query of a query.

                                      • 16. Re: query sum issue
                                        JohnGree Level 1
                                        ok yes but, my cfoutput is a query to display

                                        clubname, smsbought, smscost
                                        clubname, smsbought, smscost
                                        clubname, smsbought, smscost

                                        so if i have the querys seperate, how can i loop through each club and show smsbought and smscost next to them?
                                        • 17. query sum issue
                                          BKBK Adobe Community Professional & MVP
                                          One can think immediately of a query of queries

                                          <cfquery name="q1" datasource="dsn">
                                          select CT.ClubName as club, PTS.clubid as clubID, SUM(PTS.SMS_Amount) AS SMSBought
                                          from SMS_Clubs_Table CT, payment_table PTS
                                          where CT.clubid=PTS.clubid
                                          group by PTS.clubid
                                          </cfquery>

                                          <cfquery name="q2" datasource="dsn">
                                          select clubID, SUM(SR.Cost) AS SMSCost
                                          from SMS_Records SR
                                          group by clubid
                                          </cfquery>

                                          <cfquery name="q" dbtype="query">
                                          select q1.club, q1.SMSBought, q2.SMSCost
                                          from q1, q2
                                          where q1.clubID=q2.clubID
                                          </cfquery>
                                          <cfdump var="#q#">

                                          I am assuming that clubID is a primary key in the clubs table. I have avoided a query of a query involving 3 queries. I remember vaguely, but am not too sure, that the allowed limit is two.

                                          I hope this works, so that you can get going. There can always be a much more efficient solution than this, of course. I am passing the baton.




                                          • 18. Re: query sum issue
                                            JohnGree Level 1
                                            ok thanks, that works, i just need to do 1 more thing to have a order by

                                            do a sum of

                                            q1.SMSBought minus q2.SMSCost to give me SMSLeft, but how can i do this in a QofQ ?


                                            <cfquery name="GetUser" dbtype="query">
                                            select q1.club, q1.SMSBought, q2.SMSCost, q2.clubID
                                            from q1, q2
                                            where q1.clubID=q2.clubID
                                            <cfif isdefined ("URL.School")>ORDER BY q1.club</cfif>
                                            <cfif isdefined ("URL.Used")>ORDER BY q2.SMSCost</cfif>
                                            <cfif isdefined ("URL.Left")>ORDER BY SMSLeft</cfif>

                                            </cfquery>
                                            • 19. Re: query sum issue
                                              BKBK Adobe Community Professional & MVP
                                              q1.SMSBought minus q2.SMSCost to give me SMSLeft, but how can i do this

                                              Introduce aliases in the QoQ, thus

                                              <cfquery name="q" dbtype="query">
                                              select q1.club as club, q1.SMSBought as bought, q2.SMSCost as cost
                                              from q1, q2
                                              where q1.clubID=q2.clubID
                                              </cfquery>

                                              then simply do <cfoutput query="q">#bought - cost#<br></cfoutput>

                                              If you insist on having it in a query, one possibility is:
                                              <cfset SMSLeft=arraynew(1)>
                                              <cfloop query="q" >
                                              <cfset SMSLeft[currentRow]=bought-cost>
                                              </cfloop>
                                              <cfset addCol = queryAddColumn(q,"SMSLeft","double",SMSLeft)>
                                              <cfdump var="#q#">

                                              • 20. Re: query sum issue
                                                JohnGree Level 1
                                                ok thanks, i really need it in a query so i can do a sort by smsleft

                                                i tried you way but i get this error, any ideas what i need to do?

                                                Complex object types cannot be converted to simple values.
                                                The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.
                                                The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a <CFIF> tag. This was possible in ColdFusion 2.0 but creates an error in later versions.


                                                The error occurred in httpdocs\SchoolsAdmin.cfm: line 139

                                                137 : <td class="SMALLWhite"><div align="left">#SMSCost#</div></td>
                                                138 : <td class="SMALLWhite">
                                                139 : <div align="left">#smsleft#</div></td>


                                                • 21. Re: query sum issue
                                                  BKBK Adobe Community Professional & MVP
                                                  ok thanks, i really need it in a query so i can do a sort by smsleft
                                                  i tried you way but i get this error, any ideas what i need to do?


                                                  You haven't quite done it the way I suggest. Then there would be cost (not SMSCost), bought and club. In any case, there was some confusion. The name SMSLeft stands for a column and for an array. That was not the intention. Use this instead

                                                  <cfset addCol = queryAddColumn(q,"left","double",SMSLeft)>


                                                  • 22. Re: query sum issue
                                                    JohnGree Level 1
                                                    ok thanks that displays ok now.

                                                    how do i now put the order by clause in place?

                                                    i have a link that adds the word left to the url, i have <cfif isdefined ("URL.Left")>ORDER BY left</cfif> but this wwouldnt work

                                                    how else can i do it?

                                                    <cfquery name="GetUser" dbtype="query">
                                                    select q1.club, q1.SMSBought, q2.SMSCost, q2.clubID
                                                    from q1, q2
                                                    where q1.clubID=q2.clubID
                                                    <cfif isdefined ("URL.School")>ORDER BY q1.club</cfif>
                                                    <cfif isdefined ("URL.Used")>ORDER BY q2.SMSCost</cfif>
                                                    <cfif isdefined ("URL.Left")>ORDER BY left</cfif>

                                                    </cfquery>


                                                    <cfset SMSLeft=arraynew(1)>
                                                    <cfloop query="GetUser" >
                                                    <cfset SMSLeft[currentRow]=SMSbought-SMScost>
                                                    </cfloop>
                                                    <cfset addCol = queryAddColumn(GetUser,"left","double",SMSLeft)>
                                                    • 23. Re: query sum issue
                                                      BKBK Adobe Community Professional & MVP
                                                      You could just extend my suggestion of April 21 with

                                                      • 24. Re: query sum issue
                                                        JohnGree Level 1
                                                        ok thanks, i have tried that but get an error, i have attached my full code

                                                        what am i doing wrong?
                                                        • 25. Re: query sum issue
                                                          BKBK Adobe Community Professional & MVP
                                                          I wrote

                                                          select q1.club as club, q1.SMSBought as bought, q2.SMSCost as cost

                                                          • 26. Re: query sum issue
                                                            JohnGree Level 1
                                                            Hi i dont understand what you mean?
                                                            • 27. Re: query sum issue
                                                              Level 7
                                                              JohnGree wrote:
                                                              > ok thanks, i have tried that but get an error, i have attached my full code
                                                              >
                                                              > what am i doing wrong?

                                                              what's the error you are getting?

                                                              > <cfloop query="q" >
                                                              > <cfset SMSLeft[currentRow]=SMSbought-SMScost>
                                                              > </cfloop>

                                                              i think you should be using cfoutput instead of cfloop in the above...

                                                              --
                                                              Azadi Saryev
                                                              Sabai-dee.com
                                                              Vientiane, Laos
                                                              http://www.sabai-dee.com
                                                              • 28. query sum issue
                                                                JohnGree Level 1
                                                                no that is not an output, i have this table below which when the top headers of the table are clicked it should sort the output order

                                                                the error i get is

                                                                Error Executing Database Query.

                                                                Query Of Queries syntax error.
                                                                Encountered "left. Incorrect ORDER BY column reference [left].
                                                                Only simple column reference, alias name, and integer column id are allowed.
                                                                Example: You can use alias to refer to a complex expression:
                                                                SELECT (a+b)/2 as x FROM T ORDER BY x

                                                                The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\SchoolsAdmin.cfm: line 52

                                                                50 : <cfif isdefined ("URL.School")>ORDER BY club</cfif>
                                                                51 : <cfif isdefined ("URL.Used")>ORDER BY cost</cfif>
                                                                52 : <cfif isdefined ("URL.Left")>ORDER BY left</cfif>
                                                                53 : </cfquery>
                                                                54 :




                                                                so this is my full code
                                                                • 29. Re: query sum issue
                                                                  BKBK Adobe Community Professional & MVP
                                                                  BKBK:
                                                                  > I wrote
                                                                  > select q1.club as club, q1.SMSBought as bought, q2.SMSCost as cost

                                                                  JohnGree:
                                                                  Hi i dont understand what you mean?

                                                                  My query defined the aliases club, bought and cost. Yours didn't.

                                                                  • 30. Re: query sum issue
                                                                    JohnGree Level 1
                                                                    ok so what is the actual query i need to order by smsleft?

                                                                    • 31. Re: query sum issue
                                                                      Level 7

                                                                      <cfoutput> does not necessarily output thing on screen...

                                                                      anyway, what's the error you are getting???

                                                                      --
                                                                      Azadi Saryev
                                                                      Sabai-dee.com
                                                                      Vientiane, Laos
                                                                      http://www.sabai-dee.com
                                                                      • 32. Re: query sum issue
                                                                        BKBK Adobe Community Professional & MVP
                                                                        ok so what is the actual query i need to order by smsleft?
                                                                        In your code of April 30, just change the line

                                                                        select q1.club, q1.SMSBought, q2.SMSCost, q2.clubID

                                                                        into

                                                                        select q1.club as club, q1.SMSBought as bought, q2.SMSCost as cost

                                                                        and see what happens.



                                                                        • 33. Re: query sum issue
                                                                          JohnGree Level 1
                                                                          ok i have changed the aliases but i still get th same error which is

                                                                          Error Executing Database Query.

                                                                          Query Of Queries syntax error.
                                                                          Encountered "left. Incorrect ORDER BY column reference [left].
                                                                          Only simple column reference, alias name, and integer column id are allowed.
                                                                          Example: You can use alias to refer to a complex expression:
                                                                          SELECT (a+b)/2 as x FROM T ORDER BY x

                                                                          The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\SchoolsAdmin.cfm: line 52

                                                                          50 : <cfif isdefined ("URL.School")>ORDER BY club</cfif>
                                                                          51 : <cfif isdefined ("URL.Used")>ORDER BY cost</cfif>
                                                                          52 : <cfif isdefined ("URL.Left")>ORDER BY left</cfif>
                                                                          53 : </cfquery>
                                                                          54 :

                                                                          • 34. Re: query sum issue
                                                                            Level 7
                                                                            "LEFT" is a reserved word. do not use it in variable names or you will
                                                                            get into trouble... like you did...
                                                                            --

                                                                            Azadi Saryev
                                                                            Sabai-dee.com
                                                                            http://www.sabai-dee.com
                                                                            • 35. Re: query sum issue
                                                                              BKBK Adobe Community Professional & MVP
                                                                              Azadi:
                                                                              "LEFT" is a reserved word

                                                                              Thanks for spotting it. JohnGree could of course replace every occurrence of left with a non-reserved word.


                                                                              • 36. Re: query sum issue
                                                                                JohnGree Level 1
                                                                                ok i have changed the word left to smsleft, but i still get the same error,

                                                                                Complex object types cannot be converted to simple values.
                                                                                The error occurred in D:\inetpub\vhosts\thesmsengine.com\httpdocs\SchoolsAdmin.cfm: line 146

                                                                                144 : <td class="SMALLWhite"><div align="left">#Cost#</div></td>
                                                                                145 : <td class="SMALLWhite">
                                                                                146 : <div align="left">#smsleft#</div></td>





                                                                                <cfquery name="q1" datasource="#application.ds#">
                                                                                select CT.ClubName as club, PTS.clubid as clubID, SUM(PTS.SMS_Amount) AS SMSBought
                                                                                from SMS_Clubs_Table CT, payment_table PTS
                                                                                where CT.clubid=PTS.clubid AND School = 1
                                                                                group by PTS.clubid
                                                                                </cfquery>

                                                                                <cfquery name="q2" datasource="#application.ds#">
                                                                                select clubID, SUM(SR.Cost) AS SMSCost
                                                                                from SMS_Records SR
                                                                                group by clubid
                                                                                </cfquery>

                                                                                <cfquery name="q" dbtype="query">
                                                                                select q1.club as club, q1.SMSBought as bought, q2.SMSCost as cost, q2.ClubID
                                                                                from q1, q2
                                                                                where q1.clubID=q2.clubID
                                                                                </cfquery>


                                                                                <cfset SMSLeft=arraynew(1)>
                                                                                <cfloop query="q" >
                                                                                <cfset SMSLeft[currentRow]=bought-cost>
                                                                                </cfloop>
                                                                                <cfset addCol = queryAddColumn(q,"left","double",SMSLeft)>


                                                                                <cfquery name="GetUser" dbtype="query">
                                                                                select *
                                                                                from q
                                                                                <cfif isdefined ("URL.School")>ORDER BY club</cfif>
                                                                                <cfif isdefined ("URL.Used")>ORDER BY cost</cfif>
                                                                                <cfif isdefined ("URL.smsLeft")>ORDER BY smsleft</cfif>
                                                                                </cfquery>
                                                                                • 37. query sum issue
                                                                                  BKBK Adobe Community Professional & MVP
                                                                                  You could see that smsleft is an array. We have advised you to replace the keyword "left", but you haven't. All you had to do is simply replace "left" by, say, "elft". That is,

                                                                                  <cfset addCol = queryAddColumn(q,"elft","double",SMSLeft)>
                                                                                  <cfif isdefined ("URL.leftVar")>ORDER BY elft</cfif>