Not able to retrieve desired data from sql query

Question

SELECT     
     a.alloc_date,
     p.plan,
     p.emp_id,
      a.veh,
     a.contri_type,
     a.amount,
     SUM (a.alloc_qty) AS sum_alloc_qty,                    -- 1000 funds distributed
     SUM (a.alloc_qty * a.amount) AS sum_alloc_value,       -- 1000*2 = 2000 
     COUNT (DISTINCT part_id) AS sum_emp_count,             -- 4 employees 
     MAX (a.alloc_qty * a.amount) AS max_value_to_one_emp,  --  600 
     null as "emp_count_with_max_value"                     --  Unable to retrieve -        idealy answer should be 3 in this example
 FROM   
     alloc a, emp p
 WHERE   
     A.alloc_date >= TO_DATE ('20111001', 'YYYYMMDD')
     AND a.emp_id = p.emp_id
GROUP BY   
     a.alloc_date,
     p.plan,
     p.emp_id,
     a.veh,
     a.contri_type,
     a.amount
ORDER BY   
     alloc_date, emp_id, amount

Here, the existing query is working like below.

Suppose, the company is distributing 1000 funds, wherein each fund's price is 2.

Now these 1000 funds are distributed amongst 4 employees.

Basic issue is to retrieve the maximum value of funds for one employee. Suppose, the distribution of fund is :

emp1=600 (300*2), emp2=600 (300*2), emp3=600 (300*2), emp4=300 (300*1)

So, here the maximum value of funds to one employee = 600.

This I am able to retrieve now by the query.

But, now the next issue is to retrieve another column (emp_count_with_max_value) which should be have the number of employees awarded this maximum value under each group.

In our example it turns out to be 3 employees. But I am unable to retrieve the same

Here I have given data for only one group. Resulting query output should be like below:

'11/12/86','abc','E25','pqr','qvr',2,1000,2000,4,600,3

Answer 1

Basically, if you rank the employees' allocations it is easy to determine who got the biggest amount. Then you need to have an outer query to calculate the number of lucky dogs.

select alloc_date,
       plan,
       emp_id,
       veh,
       contri_type,
       amount,
       sum_alloc_qty,                    
       sum_alloc_value,      
       sum_emp_count,      
       max_value_to_one_emp,  
       sum ( case when rnk = 1 then 1 else 0 end ) as emp_count_with_max_value
from (
    SELECT     a.alloc_date,
           p.plan,
           p.emp_id,
           a.veh,
           a.contri_type,
           a.amount,
           SUM (a.alloc_qty) AS sum_alloc_qty,                    -- 1000 funds distributed
           SUM (a.alloc_qty * a.amount) AS sum_alloc_value,       -- 1000*2 = 2000 
           COUNT (DISTINCT part_id) AS sum_emp_count,             -- 4 employees 
           MAX (a.alloc_qty * a.amount) AS max_value_to_one_emp,  --  600 
           dense_rank() over  (order by a.alloc_qty desc)      rnk  -- rank allocation in descending order
           FROM   alloc a, emp p
       WHERE   A.alloc_date >= TO_DATE ('20111001', 'YYYYMMDD')
           AND a.emp_id = p.emp_id
    GROUP BY   a.alloc_date,
           p.plan,
           p.emp_id,
           a.veh,
           a.contri_type,
           a.amount
)
group by alloc_date,
       plan,
       emp_id,
       veh,
       contri_type,
       amount,
       sum_alloc_qty,                    
       sum_alloc_value,      
       sum_emp_count,      
       max_value_to_one_emp
ORDER BY   alloc_date,
       emp_id,
       amount

Note: in the absence of test data I haven't tested this code, and I'm not guaranteeing it to be bug-free. However the principle is sound.

Answer 2

You're going to have to group by alloc_qty too but if you use this:

count(*) keep (dense_rank last order by A.ALLOC_QTY * A.AMOUNT) over () as "emp_count_with_max_value"

That should return the number of records matching the maximum. So, integrated into your code it should be something like:

SELECT     a.alloc_date,
       p.plan,
       p.emp_id,
       a.veh,
       a.contri_type,
       a.amount,
       SUM (a.alloc_qty) AS sum_alloc_qty,                    -- 1000 funds distributed
       SUM (a.alloc_qty * a.amount) AS sum_alloc_value,       -- 1000*2 = 2000 
       COUNT (DISTINCT part_id) AS sum_emp_count,             -- 4 employees 
       MAX (a.alloc_qty * a.amount) AS max_value_to_one_emp,  --  600 
       count(*) keep (dense_rank last order by A.ALLOC_QTY * A.AMOUNT) over () as "emp_count_with_max_value"                     --  Unable to retrieve -           idealy answer should be 3 in this example
    FROM   alloc a, emp p
   WHERE   A.alloc_date >= TO_DATE ('20111001', 'YYYYMMDD')
       AND a.emp_id = p.emp_id
GROUP BY   a.alloc_qty,
       a.alloc_date,
       p.plan,
       p.emp_id,
       a.veh,
       a.contri_type,
       a.amount
ORDER BY   alloc_date,
       emp_id,
       amount

You may also want to change your sum_emp_count to

COUNT (DISTINCT part_id) over ()

and max_value_to_one_emp to

MAX (a.alloc_qty * a.amount) over ()

As otherwise you will not get the value over all the data set.