Calculate Percentage Based on each unique column value

user_14757852 0
Viewed 59

Sorry for the long post. I have a table with following columns CandidateName, Constituency, Party, Result

CREATE TABLE `parlia`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CANDIDATE_NAME` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
  `REGION` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
  `CONSTITUENCY` varchar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
  `PARTY` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT '',
  `RESULT` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
) ENGINE = InnoDB AUTO_INCREMENT = 295 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic;


INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (31, 'OPPONG SAMUEL', 'ASHANTI', 'AKROFUOM', 'NDC', 7484);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (32, 'KWABENA APPIAH-AKROFUOM', 'ASHANTI', 'AKROFUOM', 'NPP', 10249);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (34, 'NATHONY KWAKYE-AMEYAW', 'ASHANTI', 'AKROFUOM', 'PPP', 129);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (49, 'HON ALHAJI MOHAMMED MUNTAKA MUBARAK', 'ASHANTI', 'ASAWASE', 'NDC', 43917);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (50, 'NANA OKKERE TAWIAH ANTWI', 'ASHANTI', 'ASAWASE', 'NPP', 31013);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (51, 'JERRY JOSEPH QUAYSON', 'ASHANTI', 'ASAWASE', 'PPP', 458);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (90, 'DACOSTA KOFI YEBOAH', 'ASHANTI', 'BEKWAI', 'NDP', 92);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (91, 'AKWASI GYAMFI', 'ASHANTI', 'BEKWAI', 'INDP', 656);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (92, 'DR.JOE OTENG ADJEI', 'ASHANTI', 'BOSOMTWE', 'NDC', 17235);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (93, 'HON. SIMON OSEI MENSAH', 'ASHANTI', 'BOSOMTWE', 'NPP', 34790);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (95, 'YAW WIREDU', 'ASHANTI', 'BOSOMTWE', 'PNC', 58);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (96, 'HON KWAME ADARKWAH', 'ASHANTI', 'BOSOME FREHO', 'NDC', 5467);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (97, 'KWADWO KYEI FRIMPONG', 'ASHANTI', 'BOSOME FREHO', 'NPP', 14293);
INSERT INTO `parlia`(`id`, `CANDIDATE_NAME`, `REGION`, `CONSTITUENCY`, `PARTY`, `RESULT`) VALUES (98, 'OWUSU AUGUSTINE', 'ASHANTI', 'BOSOME FREHO', 'CPP', 143);

I'd like to calculate the percentage share of each candidate into another column called SHARE based on the SUM of results from individual constituencies. Below is a query without the share column. See Linked Image

SELECT CANDIDATE_NAME, CONSTITUENCY, PARTY, RESULT
FROM parlia
WHERE REGION = 'ASHANTI'

query results- Linked Image

I did this with the ROUND function but the %Percentage share I get is using sum of all values in RESULTS column for the calculation. My target is to get the SHARE for each PARTY based on the SUM(RESULTS) of each constituency.

SELECT CANDIDATE_NAME, CONSTITUENCY, PARTY, RESULT,
ROUND((RESULT/(SELECT SUM(RESULT) FROM parlia)) * 100,2) AS `SHARE`
FROM parlia
WHERE REGION = 'ASHANTI'
mysql sql
1 Answers

You may try using analytic functions here:

SELECT *, ROUND(100.0 * RESULT / SUM(RESULT) OVER (PARTITION BY CONSTITUENCY), 2) AS SHARE_PER_CONSTITUENCY
FROM e_parlia_election;

The window function call to SUM above finds the total results for each constituency. We then normalize each record's results using this total, and convert to a two decimal percentage.

user_18632290
Related