sum union tables into one

homer.favenir · #1 (**permalink**) 06-14-08, 04:19

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....

r937 · #2 (**permalink**) 06-14-08, 08:19

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

homer.favenir · #3 (**permalink**) 06-16-08, 00:11

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!

r937 · #4 (**permalink**) 06-16-08, 00:23

yes, you can

Internet Services

Web Development

Digital Marketing

Consumer Tech