How to query on another query result?

Question

I have a problem as I am not so strong on queries.

I have a query with consists of a union of two select queries :

   SELECT em.emp_code,  
          em.emp_name,  
          COALESCE(SUM(pe.hours_allotted),0) AS hours,  
          pe.dated 
     FROM employee_master em
LEFT JOIN project_employee pe ON (pe.Emp_code = em.emp_code)
    WHERE (dated >= '2011-03-14'
      AND dated < '2011-03-20' ) 
       OR dated IS NULL 
 GROUP BY em.emp_code 
UNION
  (SELECT em.emp_code,
          em.emp_name,
          '0' AS hours,
          pe.dated
     FROM employee_master em
LEFT JOIN project_employee pe ON (pe.Emp_code = em.emp_code)
    WHERE (dated >= '2011-03-14'
      AND dated < '2011-03-20' ) 
       OR dated IS NOT NULL 
 GROUP BY em.Emp_code)
ORDER BY emp_name;

Now the result sets are returning for example as:

ecode  ename    hours 
----------------------
201   Alak basu  10 
201 alak basu    0

The first result is from first select statement of the union where hours = 10 and hours = 0 is from second select statement of union.

What I want is:

ecode     ename       hours 
----------------------------
201      alak basu     10

Like in the case there should be only one result per ecode. How to group it like summing up the hours on as group by ecode so that it gives me only one result as above?

Answer 1

You can always do something like:

select emp_code, min(emp_name) as emp_name, sum(hours)
from (
  <your original query here>
) as e
group by emp_code
order by emp_name;