16 Replies Latest reply on Feb 1, 2008 3:30 AM by BKBK

    Return Column Totals

    rubo18
      I am using cfstoredproc to pull back some results into a html table. All this is working fine (including calculating the row totals in my actual sql query), until I come to total the COLUMNS up that are returned. If it is possible, I want to do this with ColdFusion, not SQL. Surely there's a way? (All returned values are ints).

      I'm using the following (where "getTotals" is cfprocresult name of my cfstoredproc) to generate :

      <tr>
      <td>Column Heading 1</td>
      <td>Column Heading 2</td>
      <td>Column Heading 3</td>
      <td>Column Heading 5</td>
      <td>Column Heading 6</td>
      <td>TOTAL Heading</td>
      </tr>
      <cfoutput query="getTotals">
      <tr>
      <td>#int1#</td>
      <td>#int2#</td>
      <td>#int3#</td>
      <td>#int4#</td>
      <td>#int5#</td>
      <td>#TOTAL#</td>
      </tr>
      </cfoutput>

      I need to add a third <tr></tr> to now calculate the column totals.

      I've tried inserting the values returned in int1, int2, etc for each column into an array to calculate a total from that, but it is only adding the value returned last?? And I cannot change the first part to <cfloop query="getTotals"> as it doesn't recognise #int1#, etc. It just gets printed to the screen as #int1#.

      Please let me know if I haven't explained well enough.

      Thanks in advance for the help.
        • 1. Re: Return Column Totals
          rubo18 Level 1
          I got this working with SQL, but would still be interested in a possible CF workaround if it's out there, thanks :)
          • 2. Re: Return Column Totals
            -==cfSearching==- Level 4
            One option is run a QoQ to get the SUM() of the columns. Though QoQ can be quirky at times with things like null values, etcetera. You could also use variables to keep a running total as you loop through the query.

            <cfquery name="getGrandTotals" dbtype="query">
            SELECT

            SUM(CAST(int1 AS integer)) AS int1,
            SUM(CAST(int2 AS integer)) AS int2,
            SUM(CAST(int3 AS integer)) AS int3,
            SUM(CAST(int4 AS integer)) AS int4,
            SUM(CAST(int5 AS integer)) AS int5,
            SUM(CAST(Total AS integer)) AS Total
            FROM getTotals
            </cfquery>

            Though I would probably do it with sql.
            • 3. Re: Return Column Totals
              BKBK Adobe Community Professional & MVP
              Rubo18 wrote
              I got this working with SQL, but would still be interested in a possible CF workaround

              Suppose the name of the (numeric) column you wish to sum is myCol. Then this should do in CFML

              <cfset sumCol = 0>
              <cfloop query="getTotals">
              <cfset sumCol = sumCol + myCol[getTotals.currentRow]>
              </cfloop>
              column sum: <cfoutput>#sumCol#</cfoutput>

              • 4. Re: Return Column Totals
                Dan Bracuk Level 5
                YourSum = arraysum(NameOfQuery["NameOfColumn"]);
                • 5. Re: Return Column Totals
                  -==cfSearching==- Level 4
                  quote:

                  Originally posted by: rubo18
                  I got this working with SQL, but would still be interested in a possible CF workaround if it's out there, thanks :)


                  As you can see you have a few choices :) ArraySum is an equally valid option. Though you may need a few more functions to convert the query column to an array.

                  <cfset columnTotal = ArraySum(ListToArray(ValueList(yourQuery.ColumnName)))>
                  • 6. Re: Return Column Totals
                    Dan Bracuk Level 5
                    quote:

                    Originally posted by: -==cfSearching==-
                    quote:

                    Originally posted by: rubo18
                    I got this working with SQL, but would still be interested in a possible CF workaround if it's out there, thanks :)


                    As you can see you have a few choices :) ArraySum is an equally valid option. Though you may need a few more functions to convert the query column to an array.

                    <cfset columnTotal = ArraySum(ListToArray(ValueList(yourQuery.ColumnName)))>

                    Or you may not.

                    • 7. Re: Return Column Totals
                      -==cfSearching==- Level 4
                      Dan Bracuk,

                      I am curious. Why do you say that? I could not get arraysum(NameOfQuery["NameOfColumn"]); to work without the extra functions. That would be cool it if worked without them.
                      • 8. Re: Return Column Totals
                        Dan Bracuk Level 5
                        quote:

                        Originally posted by: -==cfSearching==-
                        Dan Bracuk,

                        I am curious. Why do you say that? I could not get arraysum(NameOfQuery["NameOfColumn"]); to work without the extra functions. That would be cool it if worked without them.

                        What happened the last time you tried it?
                        • 9. Re: Return Column Totals
                          -==cfSearching==- Level 4
                          An error. The error message suggests CF cannot implicitly convert a coldfusion.sql.QueryColumn object to an array. Does it work for you?

                          [Table (rows 3 columns ColumnName): [ColumnName: coldfusion.sql.QueryColumn@14f8035] ] is not indexable by NameOfColumn
                          • 10. Re: Return Column Totals
                            Dan Bracuk Level 5
                            quote:

                            Originally posted by: -==cfSearching==-
                            An error. The error message suggests CF cannot implicitly convert a coldfusion.sql.QueryColumn object to an array. Does it work for you?

                            [Table (rows 3 columns ColumnName): [ColumnName: coldfusion.sql.QueryColumn@14f8035] ] is not indexable by NameOfColumn

                            I copied working code and changed the variable name, query name and column name.

                            Was your syntax exactly the same as mine, including the double quotes?
                            • 11. Return Column Totals
                              -==cfSearching==- Level 4
                              Yes. I had mistakenly swapped out one of the column names. That is why it did not work. It does now. Cool.

                              Interesting stuff. I looked under the hood and discovered ArraySum accepts a java.util.List and coldfusion.sql.QueryColumn implements java.util.List. That must be how it is able to work. Learn something new every day. Though I think I will stick with QoQ or variables lest they change QueryColumn the way they did coldfusion.runtime.Struct.
                              • 12. Return Column Totals
                                BKBK Adobe Community Professional & MVP
                                Rubo18

                                There is one advantage of adding the column entries one by one to get the sum (as I did). It enables you to validate. For example, if the column myCol allows null you could do

                                <cfset colEntry = myCol[getTotals.currentRow]>
                                <cfif isNumeric(colEntry)>
                                <cfset sumCol = sumCol + colEntry>
                                </cfif>


                                edited: Rubo18 in place of -==cfSearching==-

                                • 13. Re: Return Column Totals
                                  rubo18 Level 1
                                  Thankyou everyone, I shall try some of these out! Although I found the SQL way was much easier than I anticipated it to be, so may just stick with that.

                                  Umm, do I mark you all as giving the answer?? Sorry, don't know the proper etiquette here!
                                  • 14. Re: Return Column Totals
                                    Dan Bracuk Level 5
                                    quote:

                                    Originally posted by: rubo18
                                    Thankyou everyone, I shall try some of these out! Although I found the SQL way was much easier than I anticipated it to be, so may just stick with that.

                                    Umm, do I mark you all as giving the answer?? Sorry, don't know the proper etiquette here!

                                    Thanking us was more than enough.
                                    • 15. Re: Return Column Totals
                                      -==cfSearching==- Level 4
                                      BKBK,

                                      Yes, I agree. Calculating totals within the loop is probably the most bullet proof solution. That is why I suggested it as an alternative. Though you could also use COALESCE to eliminate the nulls, or obtain the totals in sql. That is usually my first preference.
                                      • 16. Re: Return Column Totals
                                        BKBK Adobe Community Professional & MVP
                                        -==cfSearching==-

                                        I meant to direct my previous post to Rubo18, not to you. I probably didn't scroll high enough when copying the name. My apologies.