Calculating Percentage the best way alongside a count

Question

I have the following query where I'm trying to calculate a percentage column, but it isn't calculating correctly with it showing 100 for each row which is clearly incorrect.

What am I doing wrong here? I've seen many examples of calculating percentages with many different ways and I'm wondering what would be the best approach here.

Select 
DrugName, 
Occurrences,  
(Occurrences / sum(Occurrences)) * 100 as Percentage
from
(
select 
D.DrugName,
count(*) as Occurrences
from
Visit V
Inner Join Drug D on 
V.DrugID = D.DrugID
where
StartDate >='01 Oct 2016' and
EndDate < '01 Jan 2017'
group by
D.DrugName
) a
group by
DrugName,

Answer 1

The best way to do this uses window functions:

select D.DrugName,
       count(*) as Occurrences,
       count(*) * 100.0 / sum(count(*)) over ()
from Visit V Inner Join
     Drug D 
     on V.DrugID = D.DrugID
where StartDate >= '2016-10-01' and
      EndDate < '2017-01-01'
group by D.DrugName;