Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    5

    Unanswered: Compare Amounts Btw 2 Tables

    Hi All,

    i have always used MS Access to do this, but can you please help with running it with MS SQL?
    So i have two tables. One is Invoice_tbl, with one account per customer. This table has 3 fields; CustomerID, InvoiceAmount, InvoiceID.

    Then second table is, Payment_tbl, with 2 fields; InvoiceID and PaymentAmount. The Payment table can have multiple payments from each customer.

    With Access, i would run a QUERY(call it PaymentTotal) against Payment_tbl, then do a "GroupBy" on InvoiceID and SUM on the "Amount" field.

    I then would create a NEW query against Invoice_tbl and INNER JOIN on Payment Total.

    How would i do this with SQL?

    thanks.
    Last edited by joesmithf1; 09-21-12 at 20:37.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by joesmithf1 View Post
    With Access, i would run a QUERY(call it PaymentTotal) against Payment_tbl, then do a "GroupBy" on CustomerID and SUM on the "Amount" field.
    that's nice, but you just said the Payment_tbl only has InvoiceID and PaymentAmount, so how can you do a "GroupBy" on CustomerID?

    if you still ahve these tables in access, please build your final query the way you're used to doing it, and then show us the sql source code for ir
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    5
    sorry...typo. Also, to make my OP a bit easier to follow, i excluded a table that i also joined in my query. Below are the actual queries that i wrote in Access. As you can see, i first do a "Group By" against the PAYMENT_tbl. Second query

    FIRST QUERY. Here I did a "group by" :

    SELECT PAYMENT_tbl.INV_ID, Sum(PAYMENT_tbl.PYMT_AMT) AS SumOfPYMT_AMT, First(PAYMENT_tbl.EMPID) AS FirstOfEMPID
    FROM PAYMENT_tbl
    GROUP BY PAYMENT_tbl.INV_ID;



    SECOND QUERY: Now i created a new query to look for the differences(balance).

    SELECT ADJ_tbl.EMPID, ADJ_tbl.DEDTYPE, ADJ_tbl.GOALAMT, INVOICE_tbl.INV_ID, INVOICE_tbl.INVAMT, Pymt_Total.SumOfPYMT_AMT, Pymt_Total.FirstOfEMPID
    FROM (ADJ_tbl INNER JOIN INVOICE_tbl ON ADJ_tbl.ADJID = INVOICE_tbl.ADJID) INNER JOIN Pymt_Total ON INVOICE_tbl.INV_ID = Pymt_Total.INV_ID
    WHERE (((Pymt_Total.SumOfPYMT_AMT)<>[invamt]));


    thanks!

  4. #4
    Join Date
    Sep 2006
    Posts
    5
    hi all, i think i got it; i would use "nested" SELECT statment? Here is what i got, but i kept getting the "Incorrect syntax near they keywork 'on'.......Line 9. what am i doing wrong? thanks!



    select
    [emp_id],[adj_disc].[adj_id]
    from
    [ADJInvoice].[dbo].[adj_disc] left join [ADJInvoice].[dbo].[Invoice] on [adj_disc].[adj_id]=[invoice].[adj_id]
    left join
    (select [inv_id],sum[pymt_amt]
    from [ADJInvoice].[dbo].[payment]
    group by [ADJInvoice].[dbo].[payment].[inv_id])
    on [invoice].[inv_id]=[payment].[inv_id];

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you got the error message because the subquery needs a table alias
    Code:
    SELECT a.emp_id
         , a.adj_id 
      FROM ADJInvoice.dbo.adj_disc AS a
    LEFT OUTER
      JOIN ADJInvoice.dbo.Invoice  AS i
        ON i.adj_id = a.adj_id
    LEFT OUTER
      JOIN ( select inv_id
                  , sumpymt_amt 
               from ADJInvoice.dbo.payment
             group 
                 by inv_id ) AS s
        ON s.inv_id = i.inv_id
    notice how much cleaner the query is when all tables use a short alias, and you don't clutter it with all those unnecessary square brackets

    there are still questions, though -- how come you don't apply an aggregate function on the sumpymt_amt colulmn in the subquery? how come you don't select any columns from either the Invoice table or the subquery in the main query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Sep 2006
    Posts
    5
    Hi R937, thank you for the codes. i really appreciate it. to answer your question regarding the "aggregate" function to PYMT_AMT column, i did applied that; i have it as sum[pymt.amt]. i think you might have misread my OP.

    I did not include some of the columns, b/c i wanted to first figure out the solution, than add columns that i need.

    anyway, i gave your codes a try, with the fix to sumpymt_amt. Now it look like below. But now i am getting the "Incorrect column name 'sum'.....on Line 2

    SELECT a.emp_id
    , a.adj_id
    FROM ADJInvoice.dbo.adj_disc AS a
    LEFT OUTER
    JOIN ADJInvoice.dbo.Invoice AS i
    ON i.adj_id = a.adj_id
    LEFT OUTER
    JOIN ( select inv_id
    , sum[pymt_amt]
    from ADJInvoice.dbo.payment
    group
    by inv_id ) AS s
    ON s.inv_id = i.inv_id
    Last edited by joesmithf1; 09-27-12 at 16:55.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by joesmithf1 View Post
    , sum[pymt_amt]
    that's invalid

    you're confusing parentheses and square brackets
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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