I have a table in SQL Server 2012 with data like this:
Request_Type Total_Count
---------------------------
General 25
Inquiry 15
I want to return the percentage of counts in the last column like this:
Request_Type Total_Count Total_Percentage
--------------------------------------------------
General 25 62.5
Inquiry 15 37.5
I tried the following query,
select
Request_Type, Total_Count,
(Total_Count* 100) / isnull(sum(Total_Count),0) as Total_Percentage
from
tbl_Request
but it returns 100 in the Total_Percentage
column.
Any ideas on how to achieve the result will be appreciated.
Thanks.
You can use subquery to get the SUM(Total_Count)
:
SELECT Request_Type
, Total_Count
, (Total_Count * 100) / (SELECT SUM(Total_Count) FROM tbl_Request) AS Total_Percentage
FROM tbl_Request
Use Sum Over()
trick to get the sum
of all the rows then find the percentage
. Try this..
SELECT Request_Type,
Total_Count,
( Total_Count * 100.0 ) / Sum(Total_Count)OVER()
FROM (SELECT 'General' AS Request_Type,25 AS Total_Count
UNION
SELECT 'Inquiry',15)a
Result:
Request_Type Total_Count Percentage
General 25 62.5
Inquiry 15 37.5
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With