Extract the Day of the month (dd) from a date in Query()

Question

I have a list of transactions for a month, all include a date (Column B - dd/mm/yyyy) and a £in figure (Column F). I want to create a table that shows the total taken on each day of the month so for example on the April sheet, calculate the sum of all sales on the 1st, 2nd, 3rd

=SUM(QUERY($A$3:$F, "select F where 'TEXT(B,dd)' = 1",0))

=SUM(QUERY($A$3:$F, "select F where 'TEXT(B,dd)' = 2",0))

=SUM(QUERY($A$3:$F, "select F where 'TEXT(B,dd)' = 3",0))

This completes the query successfully but returns no results, despite there being transactions on these days.

Thanks

It would also be ideal if there was a way to input the formula so it can be filled into each row upto 31, without changing the date on each row

sample sheet with relevant data

https://docs.google.com/spreadsheets/d/1nkwh1F3_3_bFdakkijjjRcKMUHJUOJ22QJKOWjzycnM/edit?usp=sharing

Answer 1

To extract the day of the month by using QUERY, use day()

Example:

=QUERY($A3:$F,"select F where day(B) = 1")

Reference

• https://developers.google.com/chart/interactive/docs/querylanguage

Answer 2

What you need is a query formula:

=query(A1:B, "select A, sum(B) where A is not null group by A order by A asc label sum(B) 'Takings'",1)

You can then format your cells to show just the day of the date or whatever you like by going to:
Format >> Number >> More formats >> More date and time formats

Functions used:

• QUERY