Mysql query issue that returns wrong SUM

Question

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

There's an aggregating function but no GROUP BY clause. Shurely shome mishtake. — Strawberry
– Strawberry, Commented Oct 10, 2013 at 8:03
@Strawberry you can have a agg func without no grouping ... but the order by makes no sense — Stephan
– Stephan, Commented Oct 10, 2013 at 8:05
order by is indeed not needed. Neither is grouping - when you don't have one it just performs a sum, not a sum per group, no issue here — AdrianBR
– AdrianBR, Commented Oct 10, 2013 at 8:14

AdrianBR · Accepted Answer · 2013-10-10 08:00:54Z

5

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

answered Oct 10, 2013 at 8:00

AdrianBR

2,5781 gold badge17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stefan Steiger Over a year ago

if you use case when instead of if, it will work on other databases as well.

thecore7 Over a year ago

AdrianBR - you saved my day man ! Genious ! Thanks, I accept your answer and will vote for it in 3 minutes :)

AdrianBR Over a year ago

@Quandary 'if' is much more cleaner visually, I like to use that for mysql. It's a statement designed for a single option case.

Collectives™ on Stack Overflow

Mysql query issue that returns wrong SUM

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related