Find percentage of column
-
Hi, I'm trying to find the percentage of a column but I'm not sure how to get the percentage correct. The attachment is how I'm getting the "Have" part.
I've tried adding this column but when I do a group by rollup it does the percentage on the overall total and not by county total.
select active_members/(select sum(active_members from counts) as percentage
-
Makes it a lot easier to help if you post consumable data in the form of CREATE TABLE scripts...
use tempdb;
go
CREATE TABLE #SampleData (
County VARCHAR(12) NOT NULL,
Gender CHAR NOT NULL,
ActiveMembers INT NOT NULL);
GO
INSERT INTO #SampleData VALUES
('Butler', 'F', 13086)
,('Butler', 'M', 11165)
,('Butler', 'U', 16)
,('Lawrence', 'F', 11464)
,('Lawrence', 'M', 9471)
,('Lawrence', 'U', 16)
,('Washington', 'F', 19984)
,('Washington', 'M', 16684)
,('Washington', 'U', 16);It's easy if you use a windowing function to get the total population of each county:
SELECT County
, Gender
, ActiveMembers
, CountyPopulation = SUM(ActiveMembers) OVER (PARTITION BY County)
, PctCountyPopulation = ActiveMembers/(1.0* SUM(ActiveMembers) OVER (PARTITION BY County))
FROM #SampleData; -
Thanks. Is there a way for the total to not be in a separate column and be in the count column instead?
-
Maybe this one is better?
SELECT x.County
, x.Gender
, x.Members
, y.TotalMembership
, RelPct = CASE WHEN Gender IS NULL THEN NULL ELSE x.Members/(1.0 * y.TotalMembership) END
FROM
(
SELECT
County
,Gender
,members = SUM(ActiveMembers)
FROM
#SampleData
GROUP BY
GROUPING SETS (
(County),
(County, Gender),
()
)) x
INNER JOIN (
SELECT County
, TotalMembership = SUM(ActiveMembers)
FROM #SampleData
GROUP BY County ) y ON x.County = y.County
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply