Clever Geek Handbook

Average with condition

I am trying to figure out how to calculate the exact mean of a variable. I currently have this request: (simplified):

select ID , Group_ID, Chargeability,Job_No_, Group_Charg FROM Data1


ID      Group ID        Chargeability       Job No_     Group Chargeability
1             a             90                c1                 88.11
1             a             90                c2                 88.11
1             a             90                c3                 88.11
1             a             90                c4                 88.11
2             a             85.6              c8                 88.11
2             a             85.6              c17                88.11
2             a             85.6              c6                 88.11

The average is not the actual value. The charge is fixed for each ID, so the average shouldn't take into account the number of rows per ID, because this is just a value replacement, since we have many jobs per ID.

Ie I would like to have Group_chargeability

= (90 + 85.6) / 2 instead of (90 * 4) + (85.6 * 3) / 7 as is currently done.

A query consists of several subqueries, some of which also call a function. This is why I cannot use the group to try and solve the problem I have.

+3


source to share


3 answers


Use the function AVG

withSUB QUERY 



SELECT d.ID, d.Group_ID, d.Chargeability, d.Job_No_, 
AVG(SELECT MIN(sub.Chargeability) 
 FROM Data1 sub
 WHERE sub.id = d.id AND sub.Job_No_ = d.Job_No_
 GROUP BY sub.ID) AS GROUP_Chargeability
FROM Data1
+2


source


You can try using the combined subquery like this:



SELECT d.ID, d.GROUP_ID, d.CHARGEABILITY, d.JOB_NO_, d2.charges 
FROM Data1 d LEFT JOIN 
(SELECT DISTINCT ID, GROUP_ID, AVG(CHARGEABILITY) AS CHARGES FROM Data1) d2 
    ON d2.ID = d.ID AND d2.GROUP_ID = d.GROUP_ID
+1


source


To calculate the average cost of charging in general:

select avg(Chargeability) as avgChargeability
from (select id, avg(Chargeability) as Chargeability
      from t
      group by id
     ) id;

You can enter this value on each line using join

or subquery.

+1


source






More articles:


All Articles