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.
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
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
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.