Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    81

    Post Unanswered: A join perhaps???

    SELECT P.CreditAmount - P.InvoiceAmount AS TotalCredit, E.EmployeeID, E.FirstName, E.LastName
    FROM tblEmployee E, tblUniformPayments P
    WHERE E.EmployeeID = P.EmployeeID;

    Results in each payment being displayed. It does not take into consideration that an employee may have multiple invoices. As a result, it shows something like the following:

    +-------------+------------+-----------+------------+
    | TotalCredit | EmployeeID | FirstName | LastName |
    +-------------+------------+-----------+------------+
    | 30 | 1 | Frank | Barns |
    | -25 | 1 | Frank | Barns |
    | -10 | 2 | Jenny | Haslebrook |
    | 60 | 3 | Deloris | Gill |
    | 45 | 4 | Bruce | Lisit |
    | 61 | 5 | Greg | Groops |
    +-------------+------------+-----------+------------+
    (All names are ficticious)

    How do I make it amalgamate the data together so that when it displays employee Frank Barns, it shows the TotalCredit as 5?

  2. #2
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32

    Not join, Grouping

    Hope this works for you!!!

    You have to do a SUM of the data in Column 1, Grouping by the employee using their ID.

    Code:
    SELECT SUM(P.CreditAmount - P.InvoiceAmount) AS TotalCredit, E.EmployeeID, E.FirstName, E.LastName
    FROM tblEmployee E, tblUniformPayments P
    WHERE E.EmployeeID = P.EmployeeID;
    GROUP BY E.EmployeeID, E.FirstName, E.LastName
    -----------------------------------
    Smith Hayward
    -----------------------------------

  3. #3
    Join Date
    Oct 2003
    Posts
    81

    Smile

    Thanks, that worked... apart from needing to place the ; at the end of the fourth line instead of the end of the third

  4. #4
    Join Date
    Apr 2003
    Location
    Edison, NJ / Oakland, NJ (Work)
    Posts
    32
    Oh yeah...
    -----------------------------------
    Smith Hayward
    -----------------------------------

Posting Permissions

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