0

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

Spring
  • 1
  • Storing the intermediate results in a temp table seems like a good approach. Take a look at https://stackoverflow.com/questions/2671518/how-do-you-create-a-temporary-table-in-an-oracle-database?rq=1 for additional info on creating temp tables. The ROLLUP command may also be of use to you here. Take a look at https://www.oracletutorial.com/oracle-basics/oracle-rollup/. – mcating Feb 10 '22 at 14:22
  • I had read that post you reference before too and tried the syntax example, however, still get following error: SQL0104 - Token TEMPORARY was not valid. Valid tokens: VIEW. – Spring Feb 10 '22 at 14:44
  • The rollup feature was a good idea, but doesn't exactly return the results I'm looking for. I haven't found how to create a temporary table, but I can just create a table. I know this is not ideal, but do you know if there Is there any harm in doing this if I drop the table at the end of my session or query? – Spring Feb 10 '22 at 23:47
  • Technically, there's nothing wrong with creating a permanent table for the purpose of storing temp results. If you have a DBA, tell them what you intend. If it's a scheduled job, you may just want to create the table once and leave it around (TRUNCATE TABLE or delete the previous results at the start of each run). If you do create-and-drop in the same, you may need some logic early to drop the table just in case the script fails before the DROP statement in the previous run. – mcating Mar 15 '22 at 17:05

1 Answers1

0

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

Paul Trimor
  • 320
  • 3
  • 15
  • If I use the above subquery, the subquery will run before the outside query, right? Without any filters in the subquery with a where clause wouldn't that take a very long time to run if I have 10 years with of data over hundreds of companies? Also, I've seen documentation which seems to be true with my Oracle DB that a Group By clause has to contain all of the non-aggregate select columns (I get an error if I don't do this so I'm pretty sure my research is accurate). – Spring Feb 10 '22 at 18:00