Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: sum union tables into one

    hi to all,
    is there a way on how to add the sum of each query results of each tables?

    my code is:
    Code:
    select
    coding_log.last_name,
    
    format(time_to_sec(SUM(CASE WHEN coding_log.time_end <= '16:00' && coding_log.adjustment = 'meeting'
            THEN coding_log.adjustment_time ELSE 0 END))/3600,2)
            as 'Total Reg Meeting',
    
    format(time_to_sec(SUM(CASE WHEN coding_log.time_end <= '16:00' && coding_log.adjustment = 'orientation'
            THEN coding_log.adjustment_time ELSE 0 END))/3600,2)
            as 'Total Reg Orientation'
    
    from
    coding_log
    
    Group By
    coding_log.Last_Name
    
    union
    
    select
    key_entry_log.last_name,
    
    time_to_sec(SUM(CASE WHEN key_entry_log.time_end <= '16:00' && key_entry_log.adjustment = 'meeting'
            THEN key_entry_log.adjustment_time ELSE 0 END))/3600
            as 'Total Reg Meeting',
    
    format(time_to_sec(SUM(CASE WHEN key_entry_log.time_end <= '16:00' && key_entry_log.adjustment = 'orientation'
            THEN key_entry_log.adjustment_time ELSE 0 END))/3600,2)
            as 'Total Reg Orientation'
    
    from
    key_entry_log
    
    Group By
    key_entry_log.Last_Name
    
    order by
    last_name
    please check
    i want to add (coding_log.'Total Reg Meeting', coding_log.'Total Reg Orientation') to (key_entry_log.'Total Reg Meeting', key_entry_log.'Total Reg Orientation')
    any advice, solutions?

    thanks in advance....
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query you have posted gives a result set consisting of 3 columns:

    - last_name
    - 'Total Reg Meeting'
    - 'Total Reg Orientation'

    remove the ORDER BY clause from your query, and then insert it here, as shown --
    Code:
    SELECT last_name
         , SUM(`Total Reg Meeting`) AS Sum_Reg_meeting
         , SUM(`Total Reg Orientation`) AS Sum_reg_Orientation
      FROM (
           your query goes here
           ) AS q
    GROUP
        BY last_name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    thanks for the reply...
    it works, can i insert a inner join after the FROM?

    Code:
    SELECT last_name
         , SUM(`Total Reg Meeting`) AS Sum_Reg_meeting
         , SUM(`Total Reg Orientation`) AS Sum_reg_Orientation
      FROM (
           your query goes here
           ) AS q -- can i make inner join here? --
    GROUP
        BY last_name
    thanks!
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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