madman
madman

Reputation: 153

Aggregate Functions in MySQL

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

Answers (1)

Gordon Linoff
Gordon Linoff

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

Related Questions