CODEWITHSUNDEEP
phpmysql
Sergio
Sergio

Reputation: 1239

Mysql compare values in table

I'm using the following query in order to compare values from two tables. SUM(u.val) is the value of total payments and I want to compare that value with the value of the invoice (r.p_val) and get listed only those with a lower value od SUM.

SELECT u.nmb, SUM(u.val) AS total_val,r.p_val
FROM inv u
INNER JOIN bills r ON r.nmb = u.nmb
WHERE total_val < r.p_val  
GROUP BY u.nmb

With this query I get error "Unknown column 'total_val' in 'where clause'"

I only managed to get a result with a query that compares the individual values and not the total payment. That query looks like this:

 SELECT u.nmb, u.val, SUM(u.val) AS total_val,r.p_val
 FROM inv u
 INNER JOIN bills r ON r.nmb = u.nmb
 WHERE  u.val < r.p_val  
 GROUP BY u.nmb

Is there a way to do some similar query and get the required result?

Thanks for any suggestions.

Upvotes: 0

Views: 1312

Answers (3)

Jerome WAGNER
Jerome WAGNER

Reputation: 22442

SELECT u.nmb, SUM(u.val) AS total_val,r.p_val
FROM inv u
INNER JOIN bills r ON r.nmb = u.nmb  
GROUP BY u.nmb
HAVING total_val < r.p_val

Upvotes: 1

Alex Deem
Alex Deem

Reputation: 4805

You can not use a column alias in the WHERE clause. Replace total_val with SUM(u.val) in the original query and it should work.

Upvotes: 0

agsamek
agsamek

Reputation: 9074

Change WHERE to HAVING. WHERE does not have access to columns defined in the select list nor to GROUP BY aggregations:

SELECT u.nmb, SUM(u.val) AS total_val,r.p_val
FROM inv u
INNER JOIN bills r ON r.nmb = u.nmb
GROUP BY u.nmb
HAVING SUM(u.val) < r.p_val

Upvotes: 2

Related Questions