CODEWITHSUNDEEP
sql
Joy
Joy

Reputation: 7028

How to query on another query result?

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?

Upvotes: 3

Views: 22809

Answers (2)

jdmichal
jdmichal

Reputation: 11152

If the desired result is to sum all hours for a single employee code into a single row, and the second query after the UNION will only ever return zero hours, it seems like the best solution here is to get rid of the UNION.

EDIT: After further clarification, here's what I think the SQL should probably look like:

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

What it's doing:

  1. Perform a subquery to filter all project_employee entries to the ones within the specified date range. (Note that there is no need for NULL or NOT NULL checks at all here. Either the date is in range, or it is not.)
  2. Sum the hours for each employee code generated in the subquery.
  3. Take all employees in the employee_master table, and search for matching entries in the filtered, summed project_employee subquery result set. (Since it is a LEFT JOIN, every employee in the master table will have an entry, even if none of the filtered project_employee entries matched.)
  4. In the case that there is no match, the pe.hours column will be NULL, causing the COALESCE to revert to its second value of zero.
  5. Order results by emp_name.

Upvotes: 2

Tom Micheline
Tom Micheline

Reputation: 911

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;

Upvotes: 2

Related Questions