Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2008
    Posts
    6

    Unanswered: SQL Group by problem

    Why cant I execute the following query in MsAccess-

    Code:
    SELECT SalesInvoice.ID, SalesInvoice.CustomerID, SalesInvoice.Commission, Sum(SalesInvoice.TotalAmount) AS SumOfTotalAmount
    FROM SalesInvoice
    GROUP BY SalesInvoice.CustomerID;
    It says " you tried to execute a query that doesn't include the specified expresiion "ID" as part of an aggregate function "

    But I can execute this-
    Code:
    SELECT CustomerID, Sum(TotalAmount) AS SumOfTotalAmount
    FROM SalesInvoice
    GROUP BY CustomerID;
    Can't I select multiple columns in aggregation? PLs help me.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You have to define any column NOT participating in an aggregate function in the GROUP BY clause.

    IE, if you have:

    SELECT col1, col2, col3, SUM(col4)

    your group by MUST look like this in order to execute:

    GROUP BY col1, col2, col3
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Echoing Teddy's point, and also highlighting this is not specific to Access but standard SQL. Have an article :
    http://weblogs.sqlteam.com/jeffs/jef.../20/60261.aspx
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the standard is not implemented as is in mysql

    i think mysql has an even better approach

    the following article may take a while to go through, but it is well worth it

    Debunking GROUP BY myths

    basically, the situation is this:

    in mysql, you can do this --
    Code:
    SELECT col1, col2, col3, SUM(col4)
      FROM daTable
    GROUP BY col1
    what happens is that the values of col2 and col3 are indeterminate, unless they are functionally dependent (in the same way we use functional dependency in normalization) on col1 -- if they are functionally dependent, then the values for col2 and col3 are the same for each group value of col1

    read the article, it'll open your mind a bit

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why do you keep posting mysql answers in Access?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sheer coincidence

    in this case, the article i linked to is a comprehensive analysis of the situation mentioned by several comment posts on the article you linked to, and if it's worth discussing when you link to it, then it's worth discussing even if another article happens to be mysql-based
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Aw - I can just never get you to rise

    Looks interesting - next time I get a spare hour I shall read. Based on your brief synopsis I can't think of a repost - sounds reasonable.

    Here's another one - why should I have to qualify my column names when they are included, fully qualified, in an INNER JOIN clause? Does mysql handle that?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump
    Here's another one - why should I have to qualify my column names when they are included, fully qualified, in an INNER JOIN clause? Does mysql handle that?
    example, plz

    perhaps we should start a new thread?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Misquoting can be fun!
    Quote Originally Posted by pootle flump
    Aw - I can just never get you to rise
    George
    Home | Blog

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If we're link dumping, then here's another take on GROUP BY (on the side of why it is important)

    http://weblogs.sqlteam.com/jeffs/jef.../20/60261.aspx

    I'm shocked that you've not posted this already Poots
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I'm shocked that you've not posted this already Poots
    Look at post #3, dumbbutt
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    haha, clbuttic!
    George
    Home | Blog

Posting Permissions

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