Results 1 to 4 of 4
  1. #1
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35

    Include Non-aggregated Fields in Aggregation Query

    Hi,



    I have put together the following query to aggregate BatAves. I also want the query to return balls, SR and BPercent without them being part of the aggregation. ie I just want the same results as in the sub-query.

    Can anyone point out how to do it?

    Thanks


    Code:
    SELECT sub.League, Avg(BatAve) AS LgBatAveFROM (SELECT Matches.venue, Matches.League, Round(Sum(runs)/(Count(batsmanId)-Sum(IIf([howDismissed]="not out",1,0))),2) AS BatAve, Sum(ballsFaced) AS balls, Round(Sum(runs)/Sum(ballsFaced)*100,2) AS SR, Round((Sum(ScorecardBatting.[_4s])+Sum(ScorecardBatting.[_6s]))/Sum(ballsFaced)*100,2) AS BPercent
    FROM Matches INNER JOIN ScorecardBatting ON Matches.matchId = ScorecardBatting.matchId
    GROUP BY Matches.venue, Matches.League
    )  AS sub
    GROUP BY sub.League;

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can use Max, Min, etc. If the values are different for each record in the subquery, you have to tell it which value you want with one of those. If they're the same, it won't matter which you use.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Thanks Paul, that's fixed it.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Which fields to include in a Query
    By hfreedman1957 in forum Queries
    Replies: 8
    Last Post: 05-19-2017, 07:27 PM
  2. Limiting date ranges & aggregated query
    By Forbes in forum Reports
    Replies: 25
    Last Post: 03-29-2017, 12:31 PM
  3. Replies: 3
    Last Post: 12-04-2015, 08:36 AM
  4. Aggregation Query doubt
    By akshayajmani in forum Access
    Replies: 4
    Last Post: 10-24-2012, 11:52 PM
  5. Replies: 0
    Last Post: 04-14-2012, 07:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums