CODEWITHSUNDEEP
mysqlsqldatabase
Paul Dessert
Paul Dessert

Reputation: 6389

SELECT not returning anything using HAVING clause

I'm trying to get the total percentage off and only return the matches >+ 80. However, this doesn't return any results:

SELECT * FROM products WHERE Available=1 AND Merchant='Amazon' HAVING (LowestUsedPrice - LowestNewPrice) / LowestNewPrice * 100 >= ?

Am I using HAVING correctly?

Upvotes: 0

Views: 59

Answers (4)

Esty
Esty

Reputation: 1912

As per I understand you wanna use having to filter whereas you may use just where condition.

About returning results, your query will produce syntax error. If you use as following and don't get any result then obviously it is because of conditions and your data. In that case if you provide data, you may get some help. 

SELECT * FROM products 
WHERE Available=1 
AND Merchant='Amazon' 
AND (LowestUsedPrice - LowestNewPrice) / LowestNewPrice * 100 >= ?

Upvotes: 1

phobia82
phobia82

Reputation: 1257

Your main problem is that your percentage formula is wrong. Use

((LowestUsedPrice - LowestNewPrice) / LowestNewPrice) * 100

or 

(LowestUsedPrice - LowestNewPrice) * 100 / LowestNewPrice

The way you are using will do something like 

(20 - 8) / 15 * 100

12 / 1500 = 0.008

And you need 

1200 / 15 = 80

Add this fix to the solution on other answers

Upvotes: 0

Damith
Damith

Reputation: 63065

SELECT *, (LowestUsedPrice - LowestNewPrice) / LowestNewPrice * 100 as percentage FROM products WHERE Available=1 AND Merchant='Amazon' HAVING  percentage  >= ?

Upvotes: 0

FallAndLearn
FallAndLearn

Reputation: 4135

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING is applied after the aggregation phase and must be used if you want to filter aggregate results.

Your query is wrong. What you can do is do the conditioning in where clause only.

SELECT * 
FROM products 
WHERE Available=1 
AND Merchant='Amazon' 
AND (LowestUsedPrice - LowestNewPrice) / LowestNewPrice * 100 >= ?

Upvotes: 1

Related Questions