Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Group By usage (2000 sp-3/ 98SE)

    My experience is with Oracle and "raw" SQL, so the Access interface is fairly new territory. In a tutorial exercise, the following demonstration query, based on a join of two tables, is used:

    SELECT tblStaffList.StaffID, tblStaffList.PayRate, Count(tblPayDetails.PayDate) AS CountOfPayDate, Avg(tblPayDetails.HoursWorked) AS AvgOfHoursWorked, Sum([PayRate]*[HoursWorked]) AS GrossPay
    FROM tblStaffList INNER JOIN tblPayDetails ON tblStaffList.StaffID = tblPayDetails.StaffID
    GROUP BY tblStaffList.StaffID, tblStaffList.PayRate;

    The table data is structured so that each StaffID in tblStaffList has only one PayRate. The inclusion of the tblStaffList.PayRate in the GROUP BY clause therefore seems redundant/ irrelevant to me. But if I remove it at the Design View interface, I receive an error that I
    "tried to execute a query that does not include the specified expression 'PayRate' as part of an aggregate function."

    There is no possible ambuguity with the PayRate attribute. Is this a peculiarity of Access, or has my SQL become that rusty?

    Alan

    Edited - After staring at this a bit more, I can only conclude that if a GROUP BY clause is used, then each attribute must have some sort of aggregate function "attached" to it, even another GROUP BY. If this is true, is it a requirement specific to Access?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Group By usage (2000 sp-3/ 98SE)

    I'm strictly an Access guy, but as far as I know, standard SQL requires that the columns in a select list must be in the GROUP BY expression or they must be arguments of aggregate functions.

    Some implementations of SQL may relax this requirement, but Access refuses to execute a query if the above isn't satisfied. You could use FIRST for PayRate instead of GROUP BY to take the field out of the GROUP BY section.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group By usage (2000 sp-3/ 98SE)

    Thanks Hans. That pretty much confirms what I deduced about the structure of such queries... as well the reluctant deduction that my SQL is somewhat under par. I can't quite see the significance of using FIRST to "take the field out of the GROUP BY section", but I'll give it more study time as I dive deeper into the murky waters of SQL and Access.

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Group By usage (2000 sp-3/ 98SE)

    The function FIRST([PayRate]) returns the first value of PayRate it encounters within a group of records. Since you have only one value of PayRate for any StaffID, this is THE value of PayRate. FIRST counts as an aggregate function, so using FIRST will take the field out of the GROUP BY clause. It won't change anything in the way the query works in this situation.

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Group By usage (2000 sp-3/ 98SE)

    Thanks Hans. That explains it nicely.

    Alan

Posting Permissions

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