> I have been investigating maybe using merge tables for each years worth of data as i would only ever query a months worth of data MAX so maybe this would improve performance???
Maybe. (And I would use PARTITION, not MERGE.) If the WHERE clause restricts the query based on the 'partition key' (which seems to be the case only for d.EntryDate), and you cannot make good use of indexes, then PARITION is likely to help.
> Also make it easier to archive a years worth of data too and to unarchive it if needed.
Once you PARTITION, and pruning is working, there is no good reason to deal with archiving.
(Now you have)
EXPLAIN
SELECT SQL_NO_CACHE SUM(d.`DataVal`)/1 AS Total,
g.GroupID, g.`GroupName`, g.`GroupDescription`,
`d`.`EntryDate` AS `Date`,
SUM(IFNULL(`sr`.Rate, 0) * (d.DataVal/1)) AS `Rate`,
SUM(IFNULL(`sr`.Rate, 0) * (d.DataVal/1)) AS `TotalCost`
FROM master.`channelgroup` cg
JOIN master.`groups` g ON cg.`GroupID` = g.`GroupID`
JOIN `data` d ON cg.ChannelID = d.`ChannelID`
LEFT OUTER JOIN `master`.`siterates` `sr` ON `d`.`SiteRateID` = `sr`.`SiteRateID`
WHERE (g.`GroupID` IN (157,78,87,79, 91,81))
AND ((d.EntryDate BETWEEN '2013-01-01 00:00:00' AND '2013-02-15 23:59:59'))
GROUP BY g.GroupID, LEFT(d.EntryDate, 16)
`d` needs INDEX(ChannelID, EntryDate)
If that index does not help enough, look into Summary Tables.