CODEWITHSUNDEEP
sqlsql-server
E. Kelly
E. Kelly

Reputation: 117

Totaling Expenses by Employee ID with SQL

I am working on a SELECT query to generate a specific output file that will be sent between systems relating to company expense reports. I am using data from two tables and am needing to generate a total value each employee owes the company. My query is similar to the following:

SELECT emp.Employee_ID,
       invoice.Date,
       (SELECT TOP 1 Invoice_Amount from invoice where....) as Amount_Owed
FROM employee as emp
INNER JOIN invoice_data as invoice
    on invoice.employee_id = emp.employee_id
GROUP BY emp.Employee_ID, invoice.Date

Now obviously I know there is something wrong with this query, because I am getting results such as the following:

Employee_ID | Date     | Amount_Owed
-------------------------------------
1000001     | 20190904 | 15.00
1000001     | 20190905 | 15.00
1000001     | 20190906 | 15.00
1000025     | 20190904 | 25.99
1000025     | 20190905 | 25.99
1000025     | 20190906 | 25.99

How do I change my query so that I can essentially get the invoice total for each employee by date? I have tried change the SELECT in the subquery to SELECT sum(Invoice_Amount from invoice where....), but then I always get errors from SSMS saying that it returns more than a single record so that does not seem to be valid. That same error also prevents me from just doing a SELECT without the "TOP 1" in the subquery.

This is probably a fairly simple issue, but I have spun my wheels too long without any successful attempts, so I would appreciate suggestions as to how I can get the data I want.

Upvotes: 0

Views: 363

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

Doesn't a simple aggregation work?

SELECT e.Employee_ID, id.Date,
       SUM(id.Invoice_Amount) as Amount_Owed
FROM employee e INNER JOIN
     invoice_data id
     on id.employee_id = e.employee_id
GROUP BY e.Employee_ID, id.Date

Upvotes: 2

Related Questions