Skip to main content
M
MarianneValentine
Inspiring
September 2, 2008
Question

Display total of contents of 'fields a' for records that have identical 'fields b' in common

  • September 2, 2008
  • 3 replies
  • 468 views
Please forgive me for my inability to put this in simple terms. I tried to search the forums for a solution but when you can't formulate your question in a pithy manner it's nigh impossible!

I am attaching code for a query that grabs all the records in a few tables that relate to workshops that have been attended but not yet paid for. What I want to do with them next is to add the "WorkshopCost" field for each of the records that has an identical "SchoolName" and display the result, so that we have a subtotal for each school.

I suspect this should probably be very easy but for some reason I can't figure out how it should be accomplished - if there is another posting where this is answered I would be very happy is someone would point it out! or otherwise give me a shove in the right direction.

I'm thinking I need to do something with the array created by my query...?
    This topic has been closed for replies.

    3 replies

    M
    MarianneValentineAuthor
    Inspiring
    September 3, 2008
    Thank you very much! Somehow it seems a lot less confusing when you use my variable aliases :) Easier to figure out what's going on.

    The first example looks exactly like what I thought maybe should happen but I wasn't sure how to 'say' it. The second one is more twisty & I'll have to look at it more when I'm done making my output look pretty :)

    I will drink an Aventinus in your honor - my boss will be very happy now I can move forward with this and she'll get her invoices!
    M
    MarianneValentineAuthor
    Inspiring
    September 3, 2008
    Thank you *very* kindly, dear -==cfSearching==-!!! This is exactly what I need!

    (I thought maybe I needed to do a QoQ but I'm kind of confused by it and am grateful I can wait a bit to figure it out!)
    -
    -__cfSearching__-
    Inspiring
    September 3, 2008
    You are welcome. As far as using a QoQ instead, in some ways its six of one .. half dozen of the other. There are a few variations. But if you are curious, here are two. Method 1 is simpler. However, method 2 demonstrates one of the ways you can modify a query's contents, using two QoQ's.

    HTH




    M
    MarianneValentineAuthor
    Inspiring
    September 3, 2008
    Hi -
    Is this question in the wrong part of the forum? If so, could someone move my post to the appropriate location?
    -
    -__cfSearching__-
    Inspiring
    September 3, 2008
    If I am understanding correctly, you would like to display a subtotal for each school. A simple option is to use the "group" attribute of cfquery. Then you could calculate the subtotal as you loop through the results and display it after each section. Another option is to use a QoQ.

    Keep in mind that when using "group", your sql query must order the results the same way. In other words, if you are grouping by "s_SchoolName" then your query must also ORDER BY s_SchoolName. Otherwise, the results will display incorrectly.


    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices