Reputation: 153
I have been running into some issues with where/when I can use aggregate functions in MySQL. If I have the following two simple tables:
Campaign Table (campaign_id, campagin_name, account, country)
Revenue Table (campaign_id, revenue, date)
I want to write a query to find the top account by revenue for each week:
I tried the following
SELECT account, SUM(revenue) as sum_rev
FROM campaign
JOIN revenue
ON c.campaign_id = r.campaign_id
WHERE revenue =
( SELECT revenue
FROM campaign
JOIN revenue
ON c.campaign_id = r.campaign_id
WHERE revenue = MAX(SUM(revenue))
)
GROUP BY week(date)
I was told this isn't correct, is the issue just the nesting of the aggregate function max and sum?
Upvotes: 1
Views: 106
Reputation: 1269445
In MySQL, I think variables are the simplest way:
SELECT cr.*
FROM (SELECT cr.*,
(@rn := if(@w = concat_ws('-', yyyy, wk), @rn + 1,
if(@rn := concat_ws('-', yyyy, wk), 1, 1)
)
) as rn
FROM (SELECT c.account, year(r.date) as yyyy, week(r.date) as wk, SUM(r.revenue) as sum_rev
FROM campaign c JOIN
revenue r
ON c.campaign_id = r.campaign_id
GROUP BY c.account, year(r.date), week(r.date)
ORDER BY yyyy, wk, sum_rev DESC
) cr CROSS JOIN
(SELECT @wy := '', @rn := 0) params
) cr
WHERE rn = 1;
Upvotes: 1