Different results from the same sql query depending on platform-CodePudding

The following query below gives me two different results depending on which platform I use to execute the query. In Phpmyadmin I get one result and Flyspeed another result. See screenshot: . Why would there be a difference ? Is there a setting which would cause the difference ?

Select
    untdchem_db2.customer_credits.CustID,
    untdchem_db2.customers.CustCompanyName,
    (To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) As days_past_due,
    Sum(If(((Select
         days_past_due) Between 1 And 30), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As zero_30,
    Sum(If(((Select
         days_past_due) Between 31 And 40), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As thirty_40,
    Sum(If(((Select
         days_past_due) Between 41 And 50), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As forty_50,
    Sum(If(((Select
         days_past_due) Between 51 And 60), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As fifty_60,
    Sum(If(((Select
         days_past_due) Between 61 And 90), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As sixty_90,
    Sum(If(((Select
         days_past_due) > 90), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As ninety_plus
From
    customer_credits Inner Join
    customers On customer_credits.CustID = customers.CustID
Where
    customer_credits.CustCreditTakenDate Is Null
Group By
    customer_credits.CustID;

CodePudding user response：

because days_past_due is aggregate field. and it seems that phpmyadmin calculates days_past_due first, and then caculates Sum.

Dont use days_past_due in sum.

Check this query:

Select
    untdchem_db2.customer_credits.CustID,
    untdchem_db2.customers.CustCompanyName,
    (To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) As days_past_due,
    Sum(If(((To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) Between 1 And 30), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As zero_30,
    Sum(If(((To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) Between 31 And 40), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As thirty_40,
    Sum(If(((To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) Between 41 And 50), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As forty_50,
    Sum(If(((To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) Between 51 And 60), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As fifty_60,
    Sum(If(((To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) Between 61 And 90), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As sixty_90,
    Sum(If(((To_Days(CurDate()) - To_Days(untdchem_db2.customer_credits.CustCreditDate)) > 90), untdchem_db2.customer_credits.CustCreditTotalAmount, 0)) As ninety_plus
From
    customer_credits Inner Join
    customers On customer_credits.CustID = customers.CustID
Where
    customer_credits.CustCreditTakenDate Is Null
Group By
    customer_credits.CustID;