Average with condition

Question

I am trying to figure out how to calculate the exact average of a variable. Currently I have this query: (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 calculated is not the actual one. The Chargability is fixed for each ID so that the average should not take into consideration the number of rows per ID because it is just a replacement of the value as we have lotz of job_No per each ID.

I.e I would like to have Group_chargeability = (90+85.6)/2 instead of (90*4)+(85.6*3)/7 as currently is doing.

The query is made up of several sub-queries, some of them also calling function. This is why I cannot use group by to try to solve the issue I have.

Answer 1

Use the AVG function with a SUB 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

Answer 2

You can try using a joined 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