dcsimg
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2018
    Posts
    1

    Question ORACLE SQL - Adding Results from Multiple Select Statements

    I apologize if this question is not in the correct area.

    I have eight select statements, each to get the sum of particular information. These are not simple queries, so I will not include them here. I have made up a simpler version (based on some code I found in a forum) as shown below.

    So, I have eight sums. I need a ninth sum that is the total of all eight sums.

    sample data:
    n.SFRSTCR_CREDIT_HR values are 2, 2, 2
    e.SFRSTCR_CREDIT_HR values are 3, 3, 3

    expected results:
    ResourceHours = 6
    ProjectHours = 9
    TotalHours = 15


    select t.ResourceHours, t.ProjectHours, (t.ResourceHours+t.ProjecctHours) as TotalHours
    from (select
    sum(n.SFRSTCR_CREDIT_HR) from SFRSTCR n WHERE (n.SFRSTCR_TERM_code = '200980' AND n.SFRSTCR_rsts_code like 'R%' AND n.SFRSTCR_PIDM = '33271') as ResourceHours,
    sum(e.SFRSTCR_CREDIT_HR) from SFRSTCR e WHERE (e.SFRSTCR_TERM_code = '201180' AND e.SFRSTCR_rsts_code like 'R%' AND e.SFRSTCR_PIDM = '33271') as ProjectHours
    ) t

    I receive this error message:
    Error at line 1
    ORA-00900: invalid SQL statement

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,165
    You can use temporary tables to hold the results from each query, then query the temporary table. With temporary tables,
    you can have on commit delete, hence you never need to cleanup the data, it only lasts until the commit. You don't need
    to use temporary table, it depends on how you want to do it.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width