Calculating a total using mySQL

I have a query that looks like:

SELECT
itinerary_elements.DayID
, itinerary_elements.Element
, itinerary_elements.Total_Cost
FROM itinerary_elements
INNER JOIN itinerary_days ON itinerary_elements.DayID = itinerary_days.DayID
WHERE itinerary_elements.DayID = 100

Which might give me:

Day, Element, Total Cost
100, Element 1, 50
100, Element 2, 75
100, Element 3, 125

How can I calculate and output the sum of the totals? ie 50 + 75 + 125 (250)

Thanks.

SELECT 100 AS DayID , Element , SUM(Total_Cost) AS total FROM itinerary_elements WHERE DayID = 100 GROUP BY Element
any special reason for the join? it looks superfluous, so i took it out

Thanks for that.

Its a one to many relationship, so any given Day has multiple elements. I just took out a load of fields in my post for brevity.

which ones from the itinerary_days table?

Apologies for not coming back to this sooner - I was away for a while and then catching up.

I ended up with

SELECT 
SUM(Cost_per_PAX_Unit * PAX_Unit) AS itinerary_total, itinerary_elements.ItineraryID, File_Fee_Total2, EMS_Fee_Total2
, SUM(Cost_per_PAX_Unit * PAX_Unit) + EMS_Fee_Total2 AS Quote_Total, SUM(Cost_per_PAX_UNit * PAX_Unit) / 100          AS itinerary_commission
FROM itinerary_elements
LEFT OUTER JOIN 
(SELECT 
itineraries_sg.ItineraryID
, File_Fee_PP * (Adult_Sharing + Adult_Single + Adult_Triple) AS File_Fee_Total2
, EMS_Fee_PP * (Adult_Sharing + Adult_Single + Adult_Triple) AS EMS_Fee_Total2
  FROM  itineraries_sg
  GROUP BY itineraries_sg.ItineraryID
 )
itineraries_sg ON itinerary_elements.ItineraryID = itineraries_sg.ItineraryID

So SUM(Cost_per_PAX_Unit * PAX_Unit) AS itinerary_total seemed to do what I needed.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.