I have the following code that gets all the comments made and lists them in date order where a member either started or has replied to a comment which someone else started.
What I would like to have in the results is a totalCount' in each row that tells me how many results are related to the `itemid`. I know that each result for an item will have the same totalCount number, but this is the results I am after.
Any help on this would be much appreciated.
SELECT `c`.`id`, `c`.`dateAdded`, `c`.`itemid`, `c`.`comment`, `c`.`reportedby`,
`c`.`userid` AS `posterId`, `i`.`title`,
`poster`.`fullname` AS `posterName`,`poster`.`shopName` AS `posterShopName`
FROM `itemComments` as `c`
INNER JOIN (
SELECT `itemid`, MAX(dateAdded) as dateAdded
WHERE `itemid` IN
SELECT `itemid` FROM `itemComments`
WHERE `userid` = '219'
GROUP BY `itemid`
) as X USING (itemid, dateAdded)
LEFT JOIN `users` as `poster` ON `poster`.`userid` = `c`.`userid`
LEFT JOIN `items` as `i` ON `c`.`itemid` = `i`.`itemid`
ORDER BY `c`.`dateAdded` DESC