Multiple aggregate calculations using Group by

I have a dataset, df1, where I would like to:

1. Take the average of the TotalB column based upon grouping the TotalB column.
2. I would then like to take this new column and subtract the free value to obtain the Used value

df1

date        name    rack    TotalB  freeB   
11/20/2020  a       yes     11      5   
11/20/2020  a       yes     10      5   
11/20/2020  a       yes     12      5   
11/20/2020  a       yes     10      5   
11/20/2020  b       no      5       2   
11/20/2020  b       no      5       2   
11/20/2020  b       no      6       2   

Desired Outcome

date        name    rack    TotalB  freeB   UsedB
11/20/2020  a       yes     10.75       5    5.75
11/20/2020  b       no      5.33        2    3.33

What I am doing:

SELECT AVG(TotalB) AS AVG_TotalB, date, name, rack, TotalB, freeB, (AVG_TotalB - freeB) AS UsedB
FROM df1 
GROUP BY rack

Any suggestion will be helpful.

Answer

Your query seems pretty close. Mostly you need to fix the GROUP BY:

SELECT date, name, rack,
       AVG(TotalB) AS AVG_TotalB, freeB,
       (AVG(TotalB) - freeB) AS UsedB
FROM df1 
GROUP BY date, name, rack, freeB;

Note that you cannot re-use the column alias in the same SELECT. You need to repeat the expression.