• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 179
  • Last Modified:

Not finding a field in Mysql

This is following a previous question a little while back that I am finally able to get to.  Here is the link http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_24275987.html .  I am working on the User's Home where it will have a summary of what they registered, what they have paid and what they owe.  When I try to get it to work I get an error stating that "Unknown column 'reg_due' in 'field list'.

This is what I am looking for:
Registration Total: $20
Payment Total: $10
Total Owed: $10
//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

Open in new window

0
Defibber
Asked:
Defibber
  • 10
  • 10
1 Solution
 
Terry WoodsIT GuruCommented:
use this SQL instead:

//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
0
 
DefibberAuthor Commented:
That ended up in blanks, but I didn't get the error.
0
 
Terry WoodsIT GuruCommented:
Did you get some rows with blank values, or just no rows returned?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

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).

 
DefibberAuthor Commented:
I got 1 row with no values.  All together there is 11 rows and it should come out to about 285, I haven't added any payments as of yet to my test data.
0
 
DefibberAuthor Commented:
Ok, I added a payment of $ 50 and this it what the results were.

Due 285
Paid 600
Owes -315
0
 
Terry WoodsIT GuruCommented:
Sometimes null's can affect the results - you should be able to get around that issue using COALESCE like this:
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

Open in new window

0
 
DefibberAuthor Commented:
Same results.
  FYI, in case I mis-represented, the acct_nbr is variable for a SESSION cookie
0
 
Terry WoodsIT GuruCommented:
Try this:

SELECT count(*)
FROM convention_registrations AS R, convention_payments AS P
WHERE R.account_nbr = acct_nbr AND P.account_nbr = acct_nbr

If there are no rows returned, then there's a problem with your filter (not with the summing)
0
 
Terry WoodsIT GuruCommented:
> Ok, I added a payment of $ 50 and this it what the results were.

Do you still need a result when there's no payments for a registration? If so, you'll need a left outer join, not an inner join like you've got.
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

Open in new window

0
 
DefibberAuthor Commented:
It returned 12 rows,
0
 
DefibberAuthor Commented:
Sorry the code you sent at 1126 resulted in 12 rows.  The code sent at 1127 had the same results as above.  The idea is so that when the user logs in, they will see that they have either not sent money in, or we have not processed their checks.
0
 
Terry WoodsIT GuruCommented:
Can you post the results of this query, to show what data we're dealing with?

SELECT R.convention_amt, R.banquet_amt, P.payment
FROM convention_registrations AS R, convention_payments AS P
WHERE R.account_nbr = acct_nbr AND P.account_nbr = acct_nbr
0
 
DefibberAuthor Commented:
I see the problem, It is putting the $50 payment at each row.

convention_amt       banquet_amt       payment
5       20       50
5       20       50
5       20       50
5       20       50
5       20       50
5       20       50
0       20       50
0       20       50
5       20       50
5       20       50
0       20       50
5       20       50
0
 
Terry WoodsIT GuruCommented:
Do you know how to fix it? A simple subquery would probably do it.
0
 
DefibberAuthor Commented:
SELECT sum(P.payment) AS ttl_paid, (SELECT sum(R.convention_amt + R.banquet_amt) FROM convention_registrations AS R WHERE account_nbr = acct_nbr) AS reg_due, reg_due - ttl_paid AS owes FROM convention_registrations AS R, convention_payments AS P
WHERE account_nbr = acct_nbr

This is what I tried and this is what I get:

#1054 - Unknown column 'reg_due' in 'field list'
0
 
Terry WoodsIT GuruCommented:
Try:

SELECT sum(R.convention_amt + R.banquet_amt) AS reg_due,
           (select sum(P.payment) from convention_payments where account_nbr = acct_nbr) AS ttl_paid,
           (sum(R.convention_amt + R.banquet_amt) - (select sum(P.payment) from convention_payments where account_nbr = acct_nbr)) AS owes
FROM convention_registrations AS R
WHERE R.account_nbr = acct_nbr
0
 
Terry WoodsIT GuruCommented:
Sorry - one mistake in that one. Fixed here:

SELECT sum(R.convention_amt + R.banquet_amt) AS reg_due,
           (select sum(P.payment) from convention_payments where account_nbr = acct_nbr) AS ttl_paid,
           (sum(R.convention_amt + R.banquet_amt) - (select sum(payment) from convention_payments where account_nbr = acct_nbr)) AS owes
FROM convention_registrations AS R
WHERE R.account_nbr = acct_nbr
0
 
Terry WoodsIT GuruCommented:
I have assumed there can be multiple convention_registrations for one acct_nbr, and multiple convention_payments too.
0
 
DefibberAuthor Commented:
Thank you!, I was getting cross eyed, I found one other small typo, on the second line "(select sum(P.payment) from convention_payments AS P"
0
 
DefibberAuthor Commented:
That was exactly what I was looking for, That is kind of what I had in my head but could not get it out on "paper".
0
Question has a verified solution.

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 10
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now