Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    42

    Unanswered: Need help with QUERY and GROUP BY

    I have a query that looks like this:
    ----------------------------------------------------------------------------
    SELECT TOP 8 TABLEDATA.VW_DATA.COMPLETIONDATE AS COMPLETIONDATE,
    TABLEDATA.VW_DATA.TYPE,
    TABLEDATA.VW_DATA.TEAM_ID,
    AVG(TABLEDATA.VW_DATA.DURATION) AS AVGDURATION,
    TABLEDATA.VW_DATA.SITE,
    TABLEDATA.MODELS.MODEL, TABLEDATA.TEAMS.DURATION,
    TABLEDATA.TEAM_TYPE.TYPE AS TEAMTYPE
    FROM TABLEDATA.VW_DATA
    INNER JOIN
    TABLEDATA.MODELS ON
    TABLEDATA.VW_DATA.MODEL_ID = TABLEDATA.MODELS.MODEL_ID
    INNER JOIN
    TABLEDATA.TEAMS ON TABLEDATA.MODELS.MODEL_ID = TABLEDATA.TEAMS.MODEL_ID AND
    TABLEDATA.VW_DATA.TEAM_ID = TABLEDATA.TEAMS.TEAM_ID
    INNER JOIN
    TABLEDATA.TEAM_TYPE ON TABLEDATA.TEAMS.TYPE_ID = TABLEDATA.TEAM_TYPE.TYPE_ID
    WHERE (TABLEDATA.VW_DATA.DURATION > (SELECT MIN(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
    AND (TABLEDATA.VW_DATA.DURATION < (SELECT MAX(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
    GROUP BY TABLEDATA.VW_DATA.COMPLETIONDATE,
    TABLEDATA.VW_DATA.TYPE,
    TABLEDATA.VW_DATA.TEAM_ID,
    TABLEDATA.VW_DATA.SITE,
    TABLEDATA.MODELS.MODEL,
    TABLEDATA.TEAMS.DURATION,
    TABLEDATA.TEAM_TYPE.TYPE
    HAVING (TABLEDATA.VW_DATA.TYPE = 'Complete')
    AND (TABLEDATA.VW_DATA.TEAM_ID = 27)
    AND (TABLEDATA.MODELS.MODEL <> 'Model1')
    ORDER BY TABLEDATA.VW_DATA.COMPLETIONDATE DESC
    ----------------------------------------------------------------------------

    This query results in the following:
    Complete 27 88 SITEA MODELT 80 AGG 2004-05-20 00:00:00
    Complete 27 83 SITEA MODELT 80 AGG 2004-05-07 00:00:00
    Complete 27 70 SITEA MODELT 80 AGG 2004-05-01 00:00:00
    Complete 27 110 SITEA MODELT 80 AGG 2004-04-19 00:00:00
    Complete 27 87 SITEA MODELT 80 AGG 2004-03-17 00:00:00
    Complete 27 110 SITEA MODELT 80 AGG 2004-02-27 00:00:00
    Complete 27 73 SITEA MODELT 80 AGG 2004-02-26 00:00:00
    Complete 27 115 SITEA MODELT 80 AGG 2004-07-02 00:00:00

    What I need is one row per site, and I can get that when I exclude the completiondate field, but I need to sort on that field. How can I use the GROUP BY to return only one row with the team_id, avgduration, site, model, duration, and teamtype. I do not need to display the completiondate, I just need it for sorting. The avgduration column is supposed to be an average of the duration of each of the records.

    Thanks...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The simplest would be this:

    select TEAM_ID, AVGDURATION, VW_DATA.SITE, MODEL, DURATION, TYPE from (
    SELECT TOP 8 TABLEDATA.VW_DATA.COMPLETIONDATE AS COMPLETIONDATE,
    TABLEDATA.VW_DATA.TYPE,
    TABLEDATA.VW_DATA.TEAM_ID,
    AVG(TABLEDATA.VW_DATA.DURATION) AS AVGDURATION,
    TABLEDATA.VW_DATA.SITE,
    TABLEDATA.MODELS.MODEL, TABLEDATA.TEAMS.DURATION,
    TABLEDATA.TEAM_TYPE.TYPE AS TEAMTYPE
    FROM TABLEDATA.VW_DATA
    INNER JOIN
    TABLEDATA.MODELS ON
    TABLEDATA.VW_DATA.MODEL_ID = TABLEDATA.MODELS.MODEL_ID
    INNER JOIN
    TABLEDATA.TEAMS ON TABLEDATA.MODELS.MODEL_ID = TABLEDATA.TEAMS.MODEL_ID AND
    TABLEDATA.VW_DATA.TEAM_ID = TABLEDATA.TEAMS.TEAM_ID
    INNER JOIN
    TABLEDATA.TEAM_TYPE ON TABLEDATA.TEAMS.TYPE_ID = TABLEDATA.TEAM_TYPE.TYPE_ID
    WHERE (TABLEDATA.VW_DATA.DURATION > (SELECT MIN(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
    AND (TABLEDATA.VW_DATA.DURATION < (SELECT MAX(duration) FROM TABLEDATA.VW_DATA WHERE type = 'Complete' AND team_id = 27))
    GROUP BY TABLEDATA.VW_DATA.COMPLETIONDATE,
    TABLEDATA.VW_DATA.TYPE,
    TABLEDATA.VW_DATA.TEAM_ID,
    TABLEDATA.VW_DATA.SITE,
    TABLEDATA.MODELS.MODEL,
    TABLEDATA.TEAMS.DURATION,
    TABLEDATA.TEAM_TYPE.TYPE
    HAVING (TABLEDATA.VW_DATA.TYPE = 'Complete')
    AND (TABLEDATA.VW_DATA.TEAM_ID = 27)
    AND (TABLEDATA.MODELS.MODEL <> 'Model1')) x
    group by TEAM_ID, AVGDURATION, VW_DATA.SITE, MODEL, DURATION, TYPE
    ORDER BY COMPLETIONDATE DESC

    I could possibly miss something, but that's the general direction you need to go.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Nov 2002
    Posts
    42
    It looks like you've just added another group by. I cannot get the query to run.
    Also, is that "x" after the 'Model1')) supposed to be there?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are you getting errors? I converted your original query into sub-query and added GROUP BY to eliminate duplicates that you were getting in the original version.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Nov 2002
    Posts
    42
    OK, I see. I am now getting query results, but I still get a distinct row for each different completion date. I need to sort these by completiondate and then group them. I only need to return one row for each item.

  6. #6
    Join Date
    Nov 2002
    Posts
    42
    Thanks for your help, it is now working, but I am another problem.

    This is the query that works:
    ----------------------------------------------------------------------------
    select AVG(AVGDURATION) AS avgduration,SITE,MODEL,DURATION,TEAM_ID,TYPE,TEAMT YPE from (
    SELECT TOP 100 RESET.VW_ALL_DATA_AIRCRAFT.TYPE as TYPE,
    RESET.VW_ALL_DATA_AIRCRAFT.TEAM_ID as TEAM_ID, AVG(RESET.VW_ALL_DATA_AIRCRAFT.DURATION) AS AVGDURATION,
    RESET.VW_ALL_DATA_AIRCRAFT.RESETSITE as SITE, RESET.RESET_MDS_MODELS.MODEL as MODEL, RESET.RESET_TEAMS.DURATION as DURATION,
    RESET.RESET_TEAM_TYPE.TYPE AS TEAMTYPE,RESET.VW_ALL_DATA_AIRCRAFT.COMPLETIONDATE as COMPLETIONDATE
    FROM RESET.VW_ALL_DATA_AIRCRAFT INNER JOIN
    RESET.RESET_MDS_MODELS ON RESET.VW_ALL_DATA_AIRCRAFT.MODEL_ID = RESET.RESET_MDS_MODELS.MODEL_ID INNER JOIN
    RESET.RESET_TEAMS ON RESET.RESET_MDS_MODELS.MODEL_ID = RESET.RESET_TEAMS.MODEL_ID AND
    RESET.VW_ALL_DATA_AIRCRAFT.TEAM_ID = RESET.RESET_TEAMS.TEAM_ID INNER JOIN
    RESET.RESET_TEAM_TYPE ON RESET.RESET_TEAMS.TYPE_ID = RESET.RESET_TEAM_TYPE.TYPE_ID
    WHERE (RESET.VW_ALL_DATA_AIRCRAFT.DURATION >
    (SELECT MIN(duration)
    FROM reset.vw_all_data_aircraft
    WHERE type = 'Complete' AND team_id = 27))
    AND (RESET.VW_ALL_DATA_AIRCRAFT.DURATION <
    (SELECT MAX(duration)
    FROM reset.vw_all_data_aircraft
    WHERE type = 'Complete' AND team_id = 27))
    GROUP BY RESET.VW_ALL_DATA_AIRCRAFT.TEAM_ID,
    RESET.VW_ALL_DATA_AIRCRAFT.RESETSITE,
    RESET.VW_ALL_DATA_AIRCRAFT.TYPE,
    RESET.RESET_MDS_MODELS.MODEL,
    RESET.RESET_TEAMS.DURATION,
    RESET.RESET_TEAM_TYPE.TYPE, RESET.VW_ALL_DATA_AIRCRAFT.COMPLETIONDATE
    HAVING (RESET.VW_ALL_DATA_AIRCRAFT.TYPE = 'Complete') AND (RESET.VW_ALL_DATA_AIRCRAFT.TEAM_ID = 27) AND
    (RESET.RESET_MDS_MODELS.MODEL <> 'AGPU')
    ORDER BY RESET.VW_ALL_DATA_AIRCRAFT.COMPLETIONDATE DESC) x
    GROUP BY TEAM_ID,TEAMTYPE,TYPE,SITE,MODEL,DURATION
    --------------------------------------------------------------------------

    It does return only one row of data, however it groups completion dates that are the same. I realize that this is the correct behaviour for "GROUP BY", but I do not want like dates grouped. The only reason I need the the completiondate field is to return a list of the last items to be completed.

    Is there any way to do this?

    Thanks...

  7. #7
    Join Date
    Nov 2002
    Posts
    42
    I have figured this out.

    Thanks...

Posting Permissions

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