user3697498 - 2 months ago 4x
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)
Straw
Blue
``````

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

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

Returns

``````Fruit   AvgSeed
Blue    0.170940
Rasp    0.491452
Straw   1.923076
``````