SQL query to display both the sum of column and value of column in a date range

Question

I'm trying to do a SQL query to give the cash transactions by day with a sum of the total cash balance grouped by Company and Department. I can get each query to work separately, but can't figure out how to nest the summary query as a subquery successfully. Below are the two queries that work. I thought I could do a temporary table, but have not been able to get that to work either (get error message that token "temporary" , "private temporary" invalid with Oracle DB) and most of the information I've researched says you can do the same thing with a subquery. I'd prefer to do a subquery anyway if it's possible.

#sum of YTD cash balance

Select Company, department, sum(amount) as Balance

From GL_Table

Where Company in ('A','B','C') and FY = 21 and account = 'cash' and date between 1/1/2021 and 1/31/2021

Group By Company, department

#transactions by day

Select Company, Department, date, Amount

From GL_Table

Where Company in ('A','B','C') and FY = 21 and and account = 'cash' and date = 1/1/2021-1/31/2021

Group By Company, department, date

Answer 1

You can GROUP BY date and the correct date that corresponds to the most recent transaction via sub query.

SELECT Company, department, sum(amount) FROM (
    SELECT MAX(date), Company, department, amount FROM GL_TABLE GROUP BY date
) WHERE  
   Company in ('A','B','C') and FY = 21 and account = 'cash' and date between 
   1/1/2021 and 1/31/2021
   GROUP BY
   Company, department, date

also check out: SQL: Select most recent date for each category