CODEWITHSUNDEEP
sqlsql-serveraggregation
Lynn
Lynn

Reputation: 4398

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.

Upvotes: 0

Views: 118

Answers (2)

Venkataraman R
Venkataraman R

Reputation: 12969

You need to convert them to DECIMAL to avoid loss of the fractional parts.

DECLARE @test TABLE (dateval date,  name varchar(50), rack varchar(10),  TotalB int,  freeB int)

INSERt INTO @test
values
('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);

SELECT dateval,name, rack, freeB, cast(AVG(cast(totalB as decimal(5,2))) as decimal(5,2)) as TotalB, 
cast(AVG(cast(totalB as decimal(5,2))) as decimal(5,2)) - freeB as UsedB FROM @test
GROUP BY dateval,name, rack, freeB

+------------+------+------+-------+--------+-------+
|  dateval   | name | rack | freeB | TotalB | UsedB |
+------------+------+------+-------+--------+-------+
| 2020-11-20 | a    | yes  |     5 |  10.75 |  5.75 |
| 2020-11-20 | b    | no   |     2 |   5.33 |  3.33 |
+------------+------+------+-------+--------+-------+

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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.

Upvotes: 4

Related Questions