Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004

    Unanswered: Complex Query Help.. Please :)

    Hello group,

    First time post..

    I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..

    Here are the tables.

    evaluations - holds a record for each evauation that is created: evalID, evalName

    fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)

    fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average

    fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April.... or whatever they like) : optionID, optionText, fieldID

    submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID

    eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data

    Here's the problem..
    I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.

    an example of a posted evaluation will create:
    1 row in the submitted table..
    and a row in the eval_data table for each field well as

    with any luck this explanation makes sense and the problem will be easy to solve..

    any replys are greatly appreciated..


  2. #2
    Join Date
    Jul 2004
    ok.. here is some clarification..

    I started thinking about the first post and decided that much of that information was probably not needed..

    Here is the sql i'm workin with so far..
    SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText 
    FROM eval_eval_data AS d 
    INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
    INNER JOIN fields as f ON f.intField_ID = d.intField_ID 
    INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
    WHERE fe.intAverage = '1' 
    AND c.intOption_ID = '26' 
    Group By f.chEvalFieldText
    my question boils down to this..
    regarding the rows in the data table..

    can i group by a value in a liked table (f),
    and average values in the data table
    while limiting by values in teh data table as well.


    Am i on the wrong track?

    Am i totally lost?

    have i lost everybody else??


  3. #3
    Join Date
    Jun 2003
    West Palm Beach, FL

    Thumbs up

    You are right on track, good job.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  4. #4
    Join Date
    Jul 2004

    Help.. please

    me again....

    I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..

    here is some example data from the data table..

    intSub     intField_ID       chData
    1              1              1
    1              2              1
    1              3              4
    1              4              2
    1              26             27
    2              1              1
    2              2              2
    2              3              3
    2              4              5
    2              26            28
    intField_ID is a key that relates to fields in the evaluation
    chData is the value that was submitted and intSubmission indicates which submission the data came from.
    In this case the field_ids 1-4 ratings 1- 5 that will be averaged
    the average will be grouped by the field ID no biggie.. here's where i'm stumped.
    I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.

    make sense?
    there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
    for the data above .. i need to

    Average the values for the indicated fields grouped by the field id..
    but only where the value for fieldID = 26 are equal..

    any help is greatly appreciated.
    If you need more info.. please let me know..


Posting Permissions

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