Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: Adding attributes from several rows given a fixed attribute

    Hi
    This is my situation
    I have a database with two tables
    1.CampInfo
    2.SessionInfo

    Each camp has a camp name and is associated with an account.Multiple sessions which can vary in number are associated with a camp using a campID.Each session has several fees in it like Insurace ,camp fee,mediacl fee etc which are totalled into a total SESSION fees.
    Now as each camp has several sessions I want to display the total of all the session in the camp table.How do i do this?

    Also can I do stg for amount due.
    For example if a camp has an amount due it is equal to
    Amount due(present value)+total fees(of all sessions)-fees paid

    I dont know how to use epression for totalling all session with a given camp ID...How do i do it?

    Thanks

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    how about something like:

    SELECT CampID, Sum([insFee]+[medFee]+[camFee])
    AS SumOfAllSessionFeesByCampID
    FROM tblCampFees
    GROUP BY CampID;

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Sep 2006
    Posts
    4
    hey that is returning to em the sum of all sessions for all camps.....how do i get the sum based on a unique camp ID value

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    try SELECT DISTINCT ....then what izy said
    Ryan
    My Blog

  5. #5
    Join Date
    Sep 2006
    Posts
    4
    SELECT DISTINCT Sum([ALL].SessionTotal) AS SumOfSessionTotal, CampInfo.CampID
    FROM [ALL], CampInfo INNER JOIN [Session] ON CampInfo.CampID=Session.CampId
    GROUP BY CampInfo.CampID;

    This still continuews to give total for all


    SumOfSessionTotal CampID
    585520 1
    585520 2


    Here ALL is a query which contains the total fees associated with a session.
    Each camp has a rate associated with it in table and session has days in it...ALL.Session total combines these two to give a session totl.Now each camp has is to be totalled for all its sesiions

    Does that make sense?

    I can post all attributes if u want me to....

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    your query doesn't quite make sense to me..... You have a table called all? If you have a field with the total amount, why do you need to sum it? That's what your first line is telling me.
    Ryan
    My Blog

  7. #7
    Join Date
    Sep 2006
    Posts
    4
    Quote Originally Posted by rguy84
    your query doesn't quite make sense to me..... You have a table called all? If you have a field with the total amount, why do you need to sum it? That's what your first line is telling me.

    All is a query.This query returns the Total for each SESSION.I need to make total of all these totals based on a campID which isstored in each session row.

    For example say camp1
    camp 1 has 4 session say s1,s2,s3,s4
    s1 has a certain number of days and so has s2 and so on
    so s1 has a total amount SessionTotal=days*rate*participants

    Now this camp1 should show the total of all these SessionTotals...

    Hope it makes sense man...

    Limited to jsut words

  8. #8
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Ok, can you post your database, from what you are saying and what your query says, they don't match up...
    Ryan
    My Blog

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hey that is returning to em the sum of all sessions for all camps
    with a GROUP BY CampID?

    izy
    Last edited by izyrider; 09-07-06 at 04:33. Reason: unnecessary comment removed
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    this seems to be deeply confused!
    SELECT DISTINCT Sum([ALL].SessionTotal) AS SumOfSessionTotal, CampInfo.CampID
    FROM [ALL], CampInfo INNER JOIN [Session] ON CampInfo.CampID=Session.CampId
    GROUP BY CampInfo.CampID;

    simplest approach:

    include CampID in your query All (if it is not already there)
    and then -

    SELECT CampID, Sum(SessionTotal) AS SumOfSessionTotal
    FROM All
    GROUP BY CampID

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •