mysql join and sum problems

Question

Im having some trouble with this.

Here's an example of my tables.

Booking

id zone_id name excursion_id
1  2               1
2  1               1
3  2               1

The table where I have the quantities booking_price

id_booking id_price quantity
1              1       2
1              2       3
2              1       1
2              2       0
3              1       2
3              2       3

Here the zone table

Zone
id  Name
1    a
2    b
3    c

So I want to have a table like that

Zone_id Quantity
1          1  
2          10
3          0

The problem is when im joining tables and filtering by excursion_id im not getting ALL the zones.

I want to know how many people goes in each zone.

Please edit your question to include the query you have written. — Gordon Linoff
– Gordon Linoff, Commented Mar 27, 2014 at 14:36
...and for testing purposes the table structure and test data in the form of CREATE TABLE foo (.... and INSERT INTO foo ... statements would be nice, too (though the current form is more human-readable). — VolkerK
– VolkerK, Commented Mar 27, 2014 at 14:45

Abhik Chakraborty · Accepted Answer · 2014-03-27 19:14:10Z

1

I think better way of doing it is

select z.id, coalesce(sum(bp.quantity),0) as quantity
from Booking b 
right join Zone z on z.id = b.zone_id AND b.excursion_id = 1
left join booking_price bp on bp.id_booking = b.id
group by z.id

demo http://sqlfiddle.com/#!2/771f5/13

answered Mar 27, 2014 at 19:14

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

1 Comment

Subiendo Over a year ago

and if i want the grand total named as "gran total"?

Oscar Pérez · Accepted Answer · 2014-03-27 14:51:43Z

0

You could use a query like:

   SELECT zone.id as zone_id,
          sum(quantity) as Quantity
     FROM zone
LEFT JOIN Booking on Booking.zone_id = zone.id
LEFT JOIN Excursion on Excursion.id_booking = Booking.id
 GROUP BY zone.id

answered Mar 27, 2014 at 14:51

Oscar Pérez

4,3971 gold badge20 silver badges38 bronze badges

Collectives™ on Stack Overflow

mysql join and sum problems

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related