Bizar result using SUM

[cpd]

I've got a list of sundries in a relational database and there are currently 2 for this particular booking. When using the SUM function it only returns a single sundry where as without the SUM it returns both rows.

 

SELECT s.sundryID, s.name, s.cost, bs.quantity, (bs.quantity * s.cost) AS sundry_total, r.roomNo, SUM(bs.quantity * s.cost) AS total FROM bookings_sundries AS bs
LEFT JOIN sundries AS s ON s.sundryID = bs.sundryID
LEFT JOIN bookings_details AS bd ON bd.id = bs.bookingDetailsID
LEFT JOIN rooms AS r ON r.roomID = bd.roomID
WHERE b.bookingID = 1

 

I'm baffled as to why. The total column appears no problem but like I said it only shows a single row as opposed to both the rows. I would have expected both rows to be shown with the `total` appearing at the end of each row.

 

Bare in mind this is all happening in phpMyAdmin.

[The Little Guy]

That is the affect of the SUM function. If you want each one to show, you need to use a group, but then your sum will probably not have the same number.

[fenway]

If you want all the rows _and_ an aggregate expression, you'll need a subquery.

[cpd]

Yeah I was having a bad day. When I reviewed this the next day I realised what I'd been doing and I didn't want a sub query as I don't believe it would have been particularly efficient.