By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `wp_wpforms_entry_fields` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`entry_id` bigint(20) NOT NULL,
`form_id` bigint(20) NOT NULL,
`field_id` int(11) NOT NULL,
`value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `entry_id` (`entry_id`),
KEY `form_id` (`form_id`),
KEY `field_id` (`field_id`)
) ENGINE=InnoDB AUTO_INCREMENT=173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `wp_wpforms_entry_fields` (`id`, `entry_id`, `form_id`, `field_id`, `value`, `date`) VALUES
(5, 2, 549, 5, 'Abbott', '2022-09-30 13:24:47'),
(6, 2, 549, 6, 'Dane Norman', '2022-09-29 00:32:16'),
(7, 2, 549, 3, 'Male', '2022-09-29 00:32:16'),
(8, 2, 549, 7, 'Marble Hill', '2022-09-29 00:32:16'),
(9, 2, 549, 10, 'Doctor', '2022-09-29 00:32:16'),
(10, 3, 549, 5, 'Abbott', '2022-09-29 00:33:02'),
(11, 3, 549, 6, 'Helen Daphne', '2022-09-29 00:33:02'),
(12, 3, 549, 3, 'Female', '2022-09-29 00:33:02'),
(13, 3, 549, 7, 'Mc Kinnons', '2022-09-29 00:33:02'),
(14, 3, 549, 10, 'Retired', '2022-09-29 00:33:02'),
(15, 4, 549, 5, 'Abbott', '2022-09-29 00:41:58'),
(16, 4, 549, 6, 'Ishoon', '2022-09-29 00:41:58'),
(17, 4, 549, 3, 'Female', '2022-09-29 00:41:58'),
(18, 4, 549, 7, 'Marble Hill', '2022-09-29 00:41:58'),
(19, 4, 549, 10, 'Cosmotologist', '2022-09-29 00:41:58'),
(20, 5, 549, 5, 'Abbott', '2022-09-29 00:43:51'),
(21, 5, 549, 6, 'Joan Veronica', '2022-09-29 00:43:51'),
(22, 5, 549, 3, 'Female', '2022-09-29 00:43:51'),
(23, 5, 549, 7, 'Marble Shill', '2022-09-29 00:43:51'),
(24, 5, 549, 10, 'Retired', '2022-09-29 00:43:51'),
(25, 6, 549, 5, 'Abott', '2022-09-29 00:49:26'),
(26, 6, 549, 6, 'Malcolm W.Garfiled', '2022-09-29 00:49:26'),
(27, 6, 549, 3, 'Male', '2022-09-29 00:49:26'),
(28, 6, 549, 7, 'Blue Waters', '2022-09-29 00:49:26'),
(29, 6, 549, 10, 'Businessman', '2022-09-29 00:49:26'),
(30, 7, 549, 5, 'Abott', '2022-09-29 00:51:02'),
(31, 7, 549, 6, 'Marc-Daniel Philip', '2022-09-29 00:51:02'),
(32, 7, 549, 3, 'Male', '2022-09-29 00:51:02'),
(33, 7, 549, 7, 'Wetherills Estate', '2022-09-29 00:51:02'),
(34, 7, 549, 10, 'Construction', '2022-09-29 00:51:02'),
(35, 8, 549, 5, 'Abbott', '2022-09-30 13:16:54'),
(36, 8, 549, 6, 'Peter Franklyn', '2022-09-30 13:16:54'),
(37, 8, 549, 3, 'Male', '2022-09-30 13:16:54'),
(38, 8, 549, 7, 'Mc Kinnons', '2022-09-30 13:16:54'),
(39, 8, 549, 10, 'Retired', '2022-09-30 13:16:54'),
Records: 167 Duplicates: 0 Warnings: 0
/*before adding other data*/
SELECT count(*),
(SELECT COUNT(*) AS Supporters FROM `wp_wpforms_entry_fields` WHERE `value` = 'Supporter') AS Supporters,
(SELECT COUNT(*) AS Undecided FROM `wp_wpforms_entry_fields` WHERE `value` = 'Undecided') AS Undecided,
(SELECT COUNT(*) AS Opposition FROM `wp_wpforms_entry_fields` WHERE `value` = 'Opposition') AS Opposition
FROM (SELECT DISTINCT(entry_id) FROM `wp_wpforms_entry_fields`WHERE form_id = 549) a
| count(*) | Supporters | Undecided | Opposition |
|---|---|---|---|
| 29 | 6 | 3 | 2 |
/*lets add some extra data*/
INSERT INTO `wp_wpforms_entry_fields` VALUES
(175, 29, 231, 12, 'Supporter', '2022-10-05 04:24:41'),
(178, 29, 231, 15, 'No', '2022-10-05 04:24:41'),
(179, 30, 231, 5, 'Dummy', '2022-10-05 04:38:59'),
(180, 30, 231, 6, 'Anderson', '2022-10-05 04:38:59'),
(181, 30, 231, 3, 'Male', '2022-10-05 04:38:59'),
(182, 30, 231, 7, 'Yorks Village', '2022-10-05 04:38:59'),
(183, 30, 231, 10, 'Driver', '2022-10-05 04:38:59'),
(184, 30, 231, 12, 'Opposition', '2022-10-05 04:38:59'),
(185, 31, 256, 4, 'John Wilson Wilson', '2022-10-05 18:45:08'),
(186, 31, 256, 1, 'johnarre@email.com', '2022-10-05 18:45:08'),
(187, 31, 256, 6, 'Facebook', '2022-10-05 18:45:08'),
(189, 23, 231, 12, 'Supporter', '2022-10-11 06:13:06'),
(190, 11, 231, 12, 'Supporter', '2022-10-11 06:13:37'),
(191, 13, 231, 12, 'Undecided', '2022-10-11 06:15:49'),
(192, 16, 231, 12, 'Supporter', '2022-10-11 06:16:12');
Records: 15 Duplicates: 0 Warnings: 0
/*after adding some extra data, result becomes incorrect*/
SELECT count(*),
(SELECT COUNT(*) AS Supporters FROM `wp_wpforms_entry_fields` WHERE `value` = 'Supporter') AS Supporters,
(SELECT COUNT(*) AS Undecided FROM `wp_wpforms_entry_fields` WHERE `value` = 'Undecided') AS Undecided,
(SELECT COUNT(*) AS Opposition FROM `wp_wpforms_entry_fields` WHERE `value` = 'Opposition') AS Opposition
FROM (SELECT DISTINCT(entry_id) FROM `wp_wpforms_entry_fields`WHERE form_id = 549) a
| count(*) | Supporters | Undecided | Opposition |
|---|---|---|---|
| 29 | 10 | 4 | 3 |
/*after adding form_id = 549 in all correlated subquery, it's correct again*/
SELECT count(*),
(SELECT COUNT(*) AS Supporters FROM `wp_wpforms_entry_fields`
WHERE `value` = 'Supporter' AND form_id = 549) AS Supporters,
(SELECT COUNT(*) AS Undecided FROM `wp_wpforms_entry_fields`
WHERE `value` = 'Undecided' AND form_id = 549) AS Undecided,
(SELECT COUNT(*) AS Opposition FROM `wp_wpforms_entry_fields`
WHERE `value` = 'Opposition' AND form_id = 549) AS Opposition
FROM (SELECT DISTINCT(entry_id) FROM `wp_wpforms_entry_fields`WHERE form_id = 549) a
| count(*) | Supporters | Undecided | Opposition |
|---|---|---|---|
| 29 | 6 | 3 | 2 |
SELECT COUNT(DISTINCT entry_id) AS 'TotalEnrolled',
SUM(`value` = 'Supporter') AS Supporters,
SUM(`value` = 'Undecided') AS Undecided,
SUM(`value` = 'Opposition') AS Opposition
FROM `wp_wpforms_entry_fields`WHERE form_id = 549;
| TotalEnrolled | Supporters | Undecided | Opposition |
|---|---|---|---|
| 29 | 6 | 3 | 2 |
SELECT COUNT(DISTINCT entry_id) AS 'TotalEnrolled',
SUM(`value` = 'Supporter') AS Supporters,
(SUM(`value` = 'Supporter')/COUNT(DISTINCT entry_id))*100 AS 'Supporters %',
SUM(`value` = 'Undecided') AS Undecided,
(SUM(`value` = 'Undecided')/COUNT(DISTINCT entry_id))*100 AS 'Undecided %',
SUM(`value` = 'Opposition') AS Opposition,
(SUM(`value` = 'Opposition')/COUNT(DISTINCT entry_id))*100 AS 'Opposition %'
FROM `wp_wpforms_entry_fields`WHERE form_id = 549;
| TotalEnrolled | Supporters | Supporters % | Undecided | Undecided % | Opposition | Opposition % |
|---|---|---|---|---|---|---|
| 29 | 6 | 20.6897 | 3 | 10.3448 | 2 | 6.8966 |
SELECT TotalEnrolled,
Supporters,
(Supporters/TotalEnrolled)*100 AS 'Supporters %',
Undecided,
(Undecided/TotalEnrolled)*100 AS '%',
Opposition,
(Opposition/TotalEnrolled)*100 AS 'Opposition %'
FROM
(SELECT COUNT(DISTINCT entry_id) AS 'TotalEnrolled',
SUM(`value` = 'Supporter') AS Supporters,
SUM(`value` = 'Undecided') AS Undecided,
SUM(`value` = 'Opposition') AS Opposition
FROM `wp_wpforms_entry_fields`WHERE form_id = 549) a;
| TotalEnrolled | Supporters | Supporters % | Undecided | % | Opposition | Opposition % |
|---|---|---|---|---|---|---|
| 29 | 6 | 20.6897 | 3 | 10.3448 | 2 | 6.8966 |