Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    6

    Unanswered: Trouble with Sum/left Join

    I am trying to learn how to use a left join with Sum. I am able to get it to work with only the Sum(column) in the select statment but once I try to add in additional columns I get into problems. Any pointers would be appreciated

    MS ACCESS 2010

    SELECT p.pTitle, p.pID, l.pID, l.GrossAmount
    FROM project AS p
    LEFT JOIN (
    SELECT SUM([l.GrossAmount])
    FROM ledgerItem As l
    GROUP BY l.pID)
    ON p.pID = l.pID
    Last edited by jayhawk1; 03-14-12 at 20:12.

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    It would help if you indicated what the error is and/or post the query the doesn't work.
    When you add more fields do you add them the the GROUP BY clause?

    MTB

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT p.pTitle
         , p.pID
         , l.pID
         , l.GrossAmount
    FROM   project AS p
     LEFT
      JOIN (
            SELECT l.pID
                 , SUM([l.GrossAmount]) As GrossAmount
            FROM   ledgerItem
            GROUP
                BY l.pID
           )  As l
        ON p.pID = l.pID
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2012
    Posts
    6
    Thanks guys..

    The error is various but on gvee's Its 1) Invalid bracketing of name and if I remove the brackets then 2) Too few parameters. Expected 2

    The group by suggestion actually works.. although I am not sure I understand why because its not actually grouping the additional group by columns.

    Thanks!

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    My bad, I didn't spot that part
    Code:
    SUM(l.[GrossAmount]) As GrossAmount
    
    -- or...
    
    SUM(l.GrossAmount) As GrossAmount
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by jayhawk1 View Post
    The group by suggestion actually works.. although I am not sure I understand why because its not actually grouping the additional group by columns.
    To explain what's going on first run the bit between the brackets on its own i.e.
    Code:
    SELECT l.pID
         , SUM(l.GrossAmount) As GrossAmount
    FROM   ledgerItem
    GROUP
        BY l.pID
    This gives you the sum of the gross amount for each "pID".

    Now imagine you saved this query on its own. In Access you can write a query on top of another query, right? So you could in theory save this as "Query1" and then your query could become:
    Code:
    SELECT p.pTitle
         , p.pID
         , Query1.pID
         , Query1.GrossAmount
    FROM   project AS p
     LEFT
      JOIN Query1
        ON p.pID = Query1.pID
    In SQL land we call a "saved query" a VIEW.

    So what we did instead of saving it, we make it a "virtual view" or "inline view" or more technically: a subquery.

    Hope this helps. If you have any more questions or if this isn't clear then ask away
    George
    Home | Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gvee View Post
    To explain what's going on first run the bit between the brackets on its own i.e.
    Code:
    SELECT l.pID
         , SUM(l.GrossAmount) As GrossAmount
    FROM   ledgerItem
    GROUP
        BY l.pID
    that is ~not~ gonna work, george
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh deary me, that's two copy-paste mistakes in one thread!

    No aliases in the subquery!
    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
  •