sql - How do I combine 2 combined tables and then add a column that compares them? -


i trying create report compares 2 sets of data. 1 training table listing have trained. other project table listing has participated in project.

i want group trained , participants department know departments aren't participating after training.

i wrote following 2 statements show me counted number of trainees , particpants department:

shows participants counted department:

select emp.dept_manual, count(pt.sso) partic projectteam pt, employees emp emp.sso = pt.sso group emp.dept_manual order emp.dept_manual desc 

shows trainees counted department:

select emp.dept_manual, count(train.sso) trained trainingroster train, employees emp emp.sso = train.sso group emp.dept_manual order emp.dept_manual desc 

some info these 2 tables.

both projectteam & trainingroster linked employees table via sso. employees table has field dept_manual. not every sso in trainingroster in projectteam (going forward be) not every sso in projectteam has associated dept_manual in employees table.

i want combine these 2 select statements , add column divides counts give me %. should this: enter image description here

how combine listed selects , add 4th column shows divides trained participants?

thanks!

here 1 possible way. how limited database using, here's how might in oracle:

select distinct a.dept_manual, a.partic_count, a.train_count, a.train_count/a.partic_count pct ( select emp.dept_manual,    (select count(pt.sso),   projectteam pt, employees emp_partic   emp_partic.sso = pt.sso   , emp_partic.dept_manual = emp.dept_manual) partic_count,   (select count(train.sso)   trainingroster train, employees emp_train   emp_train.sso = train.sso   , emp_train.dept_manual = emp.dept_manual) train_count employees emp ) 

where in database such sql anywhere, might not require pseudo-table (the "a" in oracle version) , this:

select distinct emp.dept_manual,    (select count(pt.sso),   projectteam pt, employees emp_partic   emp_partic.sso = pt.sso   , emp_partic.dept_manual = emp.dept_manual) partic_count,   (select count(train.sso)   trainingroster train, employees emp_train   emp_train.sso = train.sso   , emp_train.dept_manaul = emp.dept_manual) train_count,   train_count/partic_count pct employees emp 

Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -