?
Solved

Not finding a field in Mysql

Posted on 2009-04-21
20
Medium Priority
?
177 Views
Last Modified: 2012-05-06
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
Comment
Question by:Defibber
  • 10
  • 10
20 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201014
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
 

Author Comment

by:Defibber
ID: 24201277
That ended up in blanks, but I didn't get the error.
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201299
Did you get some rows with blank values, or just no rows returned?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Defibber
ID: 24201340
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
 

Author Comment

by:Defibber
ID: 24201351
Ok, I added a payment of $ 50 and this it what the results were.

Due 285
Paid 600
Owes -315
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201388
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
 

Author Comment

by:Defibber
ID: 24201412
Same results.
  FYI, in case I mis-represented, the acct_nbr is variable for a SESSION cookie
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201422
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201427
> 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
 

Author Comment

by:Defibber
ID: 24201428
It returned 12 rows,
0
 

Author Comment

by:Defibber
ID: 24201439
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201447
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
 

Author Comment

by:Defibber
ID: 24201461
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201473
Do you know how to fix it? A simple subquery would probably do it.
0
 

Author Comment

by:Defibber
ID: 24201515
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201550
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
 
LVL 35

Accepted Solution

by:
Terry Woods earned 1000 total points
ID: 24201551
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 24201553
I have assumed there can be multiple convention_registrations for one acct_nbr, and multiple convention_payments too.
0
 

Author Closing Comment

by:Defibber
ID: 31573107
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
 

Author Comment

by:Defibber
ID: 24201590
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question