user3697498 user3697498 - 1 year ago 63
SQL Question

Divide Average of a Category by Average of the all categories

I have a SQL database that I essentially want to take the average of a group and divide by the average of the total. For example:

Fruit Number_of_Seeds
Rasp 50
Rasp 65
Straw 200
Blue 20
Straw 250

So I want to get something of the form:

Fruit Avg Seeds of Fruit / Avg of All Fruits
Rasp =((50+65)/2)/((50+65+200+20+250)/5)

I already have code to get the average seeds for fruit but how would I incorporate the denominator if I already have a group by Fruit statement?

Hope this makes sense. Thanks

Answer Source

Be sure to add the .0 to gain precision, AND select DISTINCT

Declare @YourTable table (Fruit varchar(50),Number_of_Seeds int)
Insert into @YourTable values
('Rasp',            50),
('Rasp',            65),
('Straw',          200),
('Blue',            20),
('Straw',           250)

Select Distinct Fruit
      ,AvgSeed= Avg(Number_of_Seeds+.0) over (Partition By Fruit)/Avg(Number_of_Seeds+.0) over ()
 From  @YourTable


Fruit   AvgSeed
Blue    0.170940
Rasp    0.491452
Straw   1.923076