CODEWITHSUNDEEP
mysqlleft-join
Yoni Hassin
Yoni Hassin

Reputation: 584

Left join with 1 result for each row in the left table - without GROUP BY

I have 2 tables: (I deleted irrelevant fields)

Traffic: id, impressions, country

Events: trafficID,sells

For each traffic row there might be 0 or more rows in events. When selecting all rows from traffic table + left join the events table to get the total SUM of sells for each traffic row - some rows in the result set will be duplicate because there are few traffic rows with more then 1 event.

The easy solution is GROUP BY traffic.id. Now lets say I want to group by country, and select the SUM of impressions and sells for this specific country, If I would GROUP by traffic.id I won't get the result set I want - and if I won't group by traffic.id I will get duplicate traffic rows and thus wrong SUM result.

Any elegant way to solve this? I am using php pdo mysql, innodb engine in case it is relevant.

Upvotes: 0

Views: 490

Answers (1)

eggyal
eggyal

Reputation: 125855

Group the Events table prior to joining?

SELECT   t.country, SUM(t.impressions), SUM(e.totalSells)
FROM     Traffic t LEFT JOIN (
           SELECT   trafficID, SUM(sells) AS totalSells
           FROM     Events
           GROUP BY trafficID
         ) e ON e.trafficID = t.id
GROUP BY t.country

Upvotes: 1

Related Questions