1

I have a timesheet dataset containing 4 columns:

Column 1 - A workers name

Column 2 - The date they worked

Column 3 - The project they worked on

Column 4 - The hours they worked on that date, on that project.

Eg

I'm just starting to learn about the QUERY function and I hope it is possible to group the lines by two columns - Name and Date - and sum the hours, regardless of the Project, worked on each date, and end up with a table like this:

enter image description here

So far I've just learned how to group by a single columns values. I'm not looking for a pivot table (at least I don't think I am) because I need to be able to use the resulting table to compare with another table (attendance data) and I think it will be easier if I keep the timesheet records on a single line.

Thanks in advance.

0

1 Answer 1

2

You can group by more than one columns in a query. You do not need a pivot table for that.
On the other hand, when using pivot in your query, the results will be presented in different ways.

Without a pivot

=QUERY(A1:D9, "select A, B, sum(D) group by A, B label sum(D) 'Hours' ",1)

Google Sheets query, sum, group by 2 columns

Using a pivot

=QUERY(A1:D9, "select B, sum(D) group by A pivot B label sum(D) 'Hours' ",1)

OR

=QUERY(A1:D9, "select B, sum(D) group by B pivot A label sum(D) 'Hours' ",1)

Google Sheets query, sum, group by, pivot

4
  • Apologies for the delayed response, and thanks for providing the correct answer @marikamitsos
    – bennals
    Feb 18, 2020 at 9:18
  • Glad it worked out. Feb 18, 2020 at 14:34
  • Note that the two formulas under "Using a pivot" are identical. I think that in the first line group by B pivot A is meant to be group by A pivot B. Jan 16, 2021 at 19:22
  • 1
    Thank you for the heads up @AnthonyHolland. It is now fixed. Jan 17, 2021 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.