I am trying to write a query using Joomla's query building methods, but I cannot manage to write the subquery into the query.
Here is my desired raw sql with php variables:
"SELECT track_type, track_city, SUM(count) as COUNT_CITY, A.track_country, B.COUNT_COUNTRY, MAX(track_date) as ToDate, MIN(track_date) as FromDate, C.name FROM azh_banner_tracks AS A LEFT JOIN ( SELECT SUM(count) as COUNT_COUNTRY, track_country FROM azh_banner_tracks WHERE banner_id = '" . (int) $bannerId . "' and track_type=2 GROUP BY track_type, track_country ) AS B on A.track_country = B.track_country INNER JOIN ( SELECT name, id from azh_banners where id = '" . (int) $bannerId . "' ) as C on A.banner_id = C.id WHERE banner_id = '" . (int) $bannerId . "' GROUP BY track_type, A.track_country, track_city, COUNT_COUNTRY ORDER BY COUNT_COUNTRY DESC, COUNT_CITY DESC, track_city"