mysql - Getting an aggregate value from a subquery with group by

Question

I am trying to calculate the rate of customer survival for 12, 24, 36 etc months (i.e., the number of still active customers/total customers). The problem is that "total customers" has to be "customers who are at least as old as the period tested" (e.g., how many customers who have started at least 12 months ago are still active after 12 months).

I have three relevant columns:

• PROD_CDE = product line
• START_DATE = start date
• END_DATE = leave date

And I have tried the following code:

select PROD_CDE, 
    (SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 11) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 11) AS survival_12m,
    (SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 23) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 23) AS survival_24m,
    (SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 35) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 35) AS survival_36m,
    (SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 47) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 47) AS survival_48m,
    (SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 59) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 59) AS survival_60m,
    (SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 71) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 71) AS survival_72m,
    (SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 83) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 83) AS survival_84m,
    (SELECT sum(CASE WHEN (TIMESTAMPDIFF(MONTH, START_DATE, END_DATE) > 95) then 1 else 0 END)/count(START_DATE) where TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 95) AS survival_96m
from TABLENAME 
where START_DATE >= '2011-01-01' 
group by PROD_CDE; 

I get this error message:

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'local.TABLENAME.START_DATE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have checked several other related questions but not been able to find a solution yet.

question from:https://stackoverflow.com/questions/65851783/getting-an-aggregate-value-from-a-subquery-with-group-by

Answer 1

Use conditional aggregation directly:

SELECT PROD_CDE, 
    SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 11) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 11) AS survival_12m,
    SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 23) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 23) AS survival_24m,
    SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 35) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 35) AS survival_36m,
    SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 47) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 47) AS survival_48m,
    SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 59) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 59) AS survival_60m,
    SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 71) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 71) AS survival_72m,
    SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 83) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 83) AS survival_84m,
    SUM(TIMESTAMPDIFF(MONTH, START_DATE, LEAST(END_DATE, NOW())) > 95) / SUM(TIMESTAMPDIFF(MONTH, START_DATE, NOW()) > 95) AS survival_96m
FROM TABLENAME 
WHERE START_DATE >= '2011-01-01' 
GROUP BY PROD_CDE