trying to get the overdue members by the due day

this is the actual query

Select members.member_Id, membertomships.memberToMship_DueDay, sum(memberToMship_Char开发者_开发知识库gePerPeriod)-sum(memberAccTran_Value) as StillDue 
from membertomships 
left join mshipoptions on membertomships.mshipOption_Id = mshipoptions.mshipOption_Id
left join members on membertomships.member_Id = members.member_Id
left join memberacctrans on memberacctrans.member_Id = members.member_Id
WHERE sum(if(memberToMship_DueDay<CURDATE(),0,memberToMship_ChargePerPeriod))>sum(memberAccTran_Value);

modified query:

Select 
    members.member_Id, 
    membertomships.memberToMship_DueDay, 
    sum(sum(memberToMship_InductionFee+memberToMship_JoinFee + (IF(mshipOption_Period='year', TIMESTAMPDIFF (YEAR ,memberToMship_StartDate, memberToMship_EndDate), TIMESTAMPDIFF (MONTH ,memberToMship_StartDate, memberToMship_EndDate)) * memberToMship_ChargePerPeriod))) - sum(memberAccTran_Value) as StillDue 
from membertomships 
left join mshipoptions on membertomships.mshipOption_Id = mshipoptions.mshipOption_Id
left join members on membertomships.member_Id = members.member_Id
left join memberacctrans on memberacctrans.member_Id = members.member_Id
group by member_Id
having sum(if(memberToMship_DueDay<today(),0,memberToMship_ChargePerPeriod))>sum(memberAccTran_Value);

note :paymenttable = memberacctrans table

got the error like this

                          Error Code: 1111
                         Invalid use of group function

would any one pls help me on this query

---

You'll make everything a lot easier if you would store for each payment also which membertomship record it refers to!

Try the following SQL Query to show all open dues:

Select member_id, max(membertomship_dueday), sum(membertomship_Totalfee)-sum(payment_Money) as StillDue
from membertomship 
left join member_table on membertomship.memberID = member_table.member_id
left join payments on payment_member_id = member_table.member_id
group by member_id
having sum(membertomship_Totalfee)>sum(payment_Money)

From there you can improve it to only calculate the fees that are already due: (I don't know which database server you use, so the syntax for the IF may be incorrect!)

Select member_id, (membertomship_dueday), sum(membertomship_Totalfee)-sum(payment_Money) as StillDue 
from membertomship 
left join member_table on membertomship.memberID = member_table.member_id
left join payments on payment_member_id = member_table.member_id
group by member_id
having sum(if(membertomship_dueday<today(),0,membertomship_Totalfee))>sum(payment_Money)

---

Not knowing the data types in your tables and assuming that you are using a simple "date" type for date fields, you could simply do this: (note where you see AS this is a created field you can rename them)

SELECT
member_overdue.member_id,
member_overdue.member_firstname,
member_overdue.member_lastname,
DATEDIFF(CURDATE(),membertomship.membertomship_dueday) AS days_diff
FROM
membertomship
Inner Join member_table AS member_overdue ON member_overdue.member_id = membertomship.member_Id
WHERE
membertomship.membertomship_dueday <  CURDATE()

Now using this data set in member_table:

member_id    member_firstname    member_lastname
1            brandon             s
2            sally               s
3            gregg               s

and this data set for membertomship:

membertomship_id    membertomship_StartDate membertomship_enddate   membertomship_Totalfee  membertomship_dueday    membertomship_paymethod member_Id
1                   6/1/2011                7/1/2011                45                  7/1/2011                cash                      1
2                   7/1/2010                8/3/2011                45                  8/3/2011                cc                        2
3                   1/1/2009                5/1/2011                45                  5/1/2011                cc                        3

Gave me this result set:

member_id    member_firstname    member_lastname    days_diff
1            brandon             s                  10
3            gregg               s                  71

remember that the select statement member_overdue is the alias i set in the inner join, a left and right is not ness here and your other table is irrelevant as long as payments made also update the membertomship table. If this is true then this simple query is all you need.