CODEWITHSUNDEEP
mysqlsql
thecore7
thecore7

Reputation: 484

Mysql query issue that returns wrong SUM

please help me with this query.. It seems simple but read below to see: Query:

select SUM(price) 
from prices_adverts 
where advert_id="1" 
  and room_type_id="2" 
  and (date >= "2013-10-10" 
  AND date <"2013-10-13") 
order by price

this query checks for price for hotel room for period between above dates. In this case for 2 days - 2013-10-10 and 2013-10-11. I have setted in the database price for 2013-10-10 - 34 euro and price 2013-10-11 - 0.00euro.

How I can make the query to return 0.00 price if even 1 day in the period has price 0.00?

I use this 0.00 value to show later that the room is not available for the specific date.. Now this query returns eur 34 as sum which is the price only for 1 day 2013-10-10 because the second day the room is not available..

I hope you understand the nature of the issue

Upvotes: 0

Views: 68

Answers (1)

AdrianBR
AdrianBR

Reputation: 2588

select if(min(price)=0, 0, SUM(price) )
from prices_adverts 
where advert_id="1" 
  and room_type_id="2" 
  and (date >= "2013-10-10" 
  AND date <"2013-10-13") 
order by price

Upvotes: 5

Related Questions