This course teaches how to install and configure Windows Server 2012 R2. It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).
//Here is the MySql Statment
SELECT sum(R.convention_amt + R.banquet_amt) AS reg_due, sum(P.payment) AS ttl_paid, reg_due - ttl_paid AS owes
FROM convention_registrations AS R, convention_payments AS P
WHERE R.account_nbr = acct_nbr AND P.account_nbr = acct_nbr
//The Error referes to "reg_due - ttl_paid AS owes"
//Here is the general Table layouts
Table convention_registrations
account_nbr, convention_amt, banquet_amt
Table convention_payments
account_nbr, payment
SELECT sum(COALESCE(R.convention_amt,0) + COALESCE(R.banquet_amt,0)) AS reg_due, sum(COALESCE(P.payment,0)) AS ttl_paid, sum(COALESCE(R.convention_amt,0) + COALESCE(R.banquet_amt,0)) - sum(COALESCE(P.payment,0)) AS owes
FROM convention_registrations AS R, convention_payments AS P
WHERE R.account_nbr = acct_nbr AND P.account_nbr = acct_nbr
SELECT sum(COALESCE(R.convention_amt,0) + COALESCE(R.banquet_amt,0)) AS reg_due, sum(COALESCE(P.payment,0)) AS ttl_paid, sum(COALESCE(R.convention_amt,0) + COALESCE(R.banquet_amt,0)) - sum(COALESCE(P.payment,0)) AS owes
FROM convention_registrations AS R left outer join convention_payments AS P on R.account_nbr = acct_nbr AND P.account_nbr = acct_nbr
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
//Here is the MySql Statment
SELECT sum(R.convention_amt + R.banquet_amt) AS reg_due, sum(P.payment) AS ttl_paid, sum(R.convention_amt + R.banquet_amt) - sum(P.payment) AS owes
FROM convention_registrations AS R, convention_payments AS P
WHERE R.account_nbr = acct_nbr AND P.account_nbr = acct_nbr