Need a Sum Query to Include Zero Totals-CodePudding

I am using Microsoft Access with a simple database that totals employees' hours for billable clients. I created a query to sum the total number of billed hours per employee. I need it to also return workers with a zero sum. I cannot figure out how to properly do so in the same query.

I am an accountant who has not written SQL in probably six years and never was especially proficient. I assume this should be a fairly easy fix for people that are more experienced. My SQL code is just what was generated by Access as I built the query in Design View. The code is:

SELECT [EYH Export].Worker, Sum([EYH Export].Hours) AS [Billed Hours]
FROM [EYH Export] INNER JOIN [Job List] ON [EYH Export].Client = [Job List].Client
WHERE ((([Job List].Billed)="Billed"))
GROUP BY [EYH Export].Worker;

It returns a list of workers with their name in the first column and the total number of billed hours in the second column. In order to include names with no billed hours, I believe that I should be using something like: COUNT ([EYH Export].Worker WHERE ([EYH Export].Client) IS NULL;

I would really, really appreciate any suggestions. Just pointing me in the right direction would be a tremendous blessing.

Thank you.

CodePudding user response：

As you're using the Job List as a filter only (WHERE ((([Job List].Billed)="Billed"))), and not in the SELECT, this may work for you...

(Moving the filter from the WHERE clause to inside the SUM() means that you don't exclude any rows, and so don't exclude workers with no billing.)

SELECT
  [EYH Export].Worker,
  SUM(CASE WHEN (([Job List].Billed)="Billed") THEN [EYH Export].Hours ELSE 0 END) AS [Billed Hours]
FROM
  [EYH Export]
INNER JOIN
  [Job List]
    ON [EYH Export].Client = [Job List].Client
GROUP BY
  [EYH Export].Worker;

If not all clients have a corresponding row in Jobs List, change the join to a LEFT JOIN...

SELECT
  [EYH Export].Worker,
  SUM(CASE WHEN (([Job List].Billed)="Billed") THEN [EYH Export].Hours ELSE 0 END) AS [Billed Hours]
FROM
  [EYH Export]
LEFT JOIN
  [Job List]
    ON [EYH Export].Client = [Job List].Client
GROUP BY
  [EYH Export].Worker;

CodePudding user response：

I will attempt to answer what you basically require. You’re essentially not wanting the solution but a path to follow, improve, I assume:

I would really, really appreciate any suggestions. Just pointing me in the right direction would be a tremendous blessing

So first, you may want to change the join as Brad suggests, as currently (deduction) one table holds all clients and one not. By doing so you will obtain even the clients that are missing, but will know who is missing, which brings us to the next bit, which is the iif (please consider nz too). You put that in a column. You will test the nulls and if it is null, sum up the 0’s.

That would be it for the path. For actual code, I would gladly give up my answer to the person who does that.

I wish you best if luck!