Aggregate Functions in MySQL

Question

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?

Gordon Linoff · Accepted Answer · 2017-03-15 02:15:07Z

1

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;

edited Mar 15, 2017 at 2:15

answered Mar 14, 2017 at 20:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Gordon Linoff Over a year ago

Any reason for an anonymous downvote on what should be the most reasonable approach?

Steven Moseley Over a year ago

you're missing a YEAR in the group. If the company makes it to year 2, this query will break.

madman Over a year ago

i dont follow your short jand or the need for a cross join @GordonLinoff

Gordon Linoff Over a year ago

@StevenMoseley . . . You are right. I added that in.

Gordon Linoff Over a year ago

@madman . . . The cross join is to assign the variables within the query.

Collectives™ on Stack Overflow

Aggregate Functions in MySQL

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related