Writing an SQL query using Aggregate Functions.

Question

Well i have posted this type of ques earlier but now i need to do it in a different way. I have a table structure :

ATT_Table : Fields : Act_ID, Assigned_To_ID, Status, Product

Act_ID is the primary key, Assigned_To_ID is referenced to Employee_Table Emp_ID. Status can be New, In process, Closed or On hold. Product can be any XYZ value.

Employee_Table : Fields : Emp_ID, F_Name, Product

Here, Emp_ID is primary key. Product is same as in ATT_Table And will Contain values In ATT_Table for each Assigned_To_ID same as it Contains For that Emp_ID. Its like if Emp_ID is 1 and Product is X,then in ATT_Table also for Assigned_To_ID 1 the Product value will be X. I know its a replica and i can avoid that.

Now what i want to do is. First I want to find the Total number of employees for a Each product. Let's say this value be A. Now I want to count the number of Employees to whom a particular activity is assigned grouped by Product and where status is Either New Or In process. Let's say i.e B. Like. If employees having F_Name C(Emp_ID = 1), D(Emp_ID = 2), E(Emp_ID = 3), F(Emp_ID = 4) belongs to product X. So my A according to this is 4. Now in ATT_Table Assigned_To_ID are 1 and 3 and there Product is same i.e X and status is 1(new) 3(In process). For 2 and 4 its closed or on hold. Now my B according to this is 2. Finally A/B will give me my 3rd value let's say H. In my query table I want to values of A, B and H. Can u please tell me how i can do this. Its a bit tricky and wasn't able to get on this one. Please help. Thanks.

Post some sample input and output.That is much easier my dear friend. :) — Teja
– Teja, Commented Mar 30, 2012 at 19:56
So how is this different from stackoverflow.com/questions/9937973/… ? — Fionnuala
– Fionnuala, Commented Mar 30, 2012 at 20:06

arturro · Accepted Answer · 2012-03-30 20:51:59Z

0

Something like this should do it:

SELECT g.product,
   a,
   b,
   g.a / g.b h
  FROM (  SELECT e.product,
             COUNT (DISTINCT assigned_to_id) a,
             COUNT (
                DISTINCT CASE
                            WHEN status IN ('new', 'in process')
                            THEN
                               assigned_to_id
                         END)
                b
        FROM    Employee_Table e
             LEFT OUTER JOIN
                ATT_Table a
             ON (e.emp_id = a.assigned_to_id)
    GROUP BY e.product) g;

You can drop LEFT OUTER JOIN and use INNER join if you do not want to get products which do not have corresponding records in ATT_Table

answered Mar 30, 2012 at 20:51

arturro

1,6061 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Writing an SQL query using Aggregate Functions.

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related