Original nested query without custom column
SELECT
source
.event_year
ASevent_year
,
source
.count
AScount
,
Question 193
.event_year
ASQuestion 193__event_year
,
Question 193
.count
AScount_2
FROM (SELECT
all_results_events_view
.event_year
ASevent_year
,
count() AScount
FROMall_results_events_view
WHERE ((lower(all_results_events_view
.event_name
) like '%3m%')
AND (NOT (lower(all_results_events_view
.chip_time
) like '%dn%')
ORall_results_events_view
.chip_time
IS NULL))
GROUP BYall_results_events_view
.event_year
ORDER BYall_results_events_view
.event_year
ASC)source
INNER JOIN
(SELECTall_registrations_events_view
.event_year
ASevent_year
,
count() AScount
FROMall_registrations_events_view
WHERE ((lower(all_registrations_events_view
.event_name
) like '%3m%')
AND (NOT (lower(all_registrations_events_view
.activity_name
) like '%captain%')
ORall_registrations_events_view
.activity_name
IS NULL))
GROUP BYall_registrations_events_view
.event_year
)Question 193
ON
source
.event_year
=Question 193
.event_year
LIMIT 1048575
Original nested query with a custom column that returns the incorrect results
SELECT
source
.event_year
ASevent_year
,
source
.count
AScount
,
source
.finish rate
ASfinish rate
,
source
.Question 193__event_year
ASQuestion 193__event_year
,
source
.count
AScount_2
FROM (SELECT
source
.event_year
ASevent_year
,
source
.count
AScount
,
((source
.count
/ CASE WHENQuestion 193
.count
= 0 THEN NULL ELSEQuestion 193
.count
END) 100) ASfinish rate
,
Question 193
.event_year
ASQuestion 193__event_year
,
Question 193
.count
AScount_2
FROM (SELECTall_results_events_view
.event_year
ASevent_year
,
count() AScount
FROMall_results_events_view
WHERE ((lower(all_results_events_view
.event_name
) like '%3m%')
AND (NOT (lower(all_results_events_view
.chip_time
) like '%dn%')
ORall_results_events_view
.chip_time
IS NULL))
GROUP BYall_results_events_view
.event_year
ORDER BYall_results_events_view
.event_year
ASC)source
INNER JOIN
(SELECT
all_registrations_events_view
.event_year
ASevent_year
,
count() AScount
FROMall_registrations_events_view
WHERE ((lower(all_registrations_events_view
.event_name
) like '%3m%')
AND (NOT (lower(all_registrations_events_view
.activity_name
) like '%captain%')
ORall_registrations_events_view
.activity_name
IS NULL))
GROUP BYall_registrations_events_view
.event_year
)Question 193
ON
source
.event_year
=Question 193
.event_year
)source
LIMIT 1048575
"Fixed"(in bold) nested query with the custom column
SELECT
source
.event_year
ASevent_year
,
source
.count
AScount
,
source
.finish rate
ASfinish rate
,
source
.Question 193__event_year
ASQuestion 193__event_year
,
source
.count_2
AScount_2
FROM (SELECT
source
.event_year
ASevent_year
,
source
.count
AScount
,
((source
.count
/ CASE WHENQuestion 193
.count
= 0 THEN NULL ELSEQuestion 193
.count
END) 100) ASfinish rate
,
Question 193
.event_year
ASQuestion 193__event_year
,
Question 193
.count
AScount_2
FROM (SELECTall_results_events_view
.event_year
ASevent_year
,
count() AScount
FROMall_results_events_view
WHERE ((lower(all_results_events_view
.event_name
) like '%3m%')
AND (NOT (lower(all_results_events_view
.chip_time
) like '%dn%')
ORall_results_events_view
.chip_time
IS NULL))
GROUP BYall_results_events_view
.event_year
ORDER BYall_results_events_view
.event_year
ASC)source
INNER JOIN
(SELECT
all_registrations_events_view
.event_year
ASevent_year
,
count() AScount
FROMall_registrations_events_view
WHERE ((lower(all_registrations_events_view
.event_name
) like '%3m%')
AND (NOT (lower(all_registrations_events_view
.activity_name
) like '%captain%')
ORall_registrations_events_view
.activity_name
IS NULL))
GROUP BYall_registrations_events_view
.event_year
)Question 193
ON
source
.event_year
=Question 193
.event_year
)source
LIMIT 1048575