user3697498 - 8 months ago 29

SQL Question

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

Answer

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