combining groups in aggregate GROUP BY functions in MySQL

Question

I have a data table that contains website traffic log information like this

date         ip     country     duration
02/12/2013   *      USA           455
02/13/2013   *      CA           1234
02/14/2013   *      Cambodia       13
02/12/2013   *      USA            12
03/11/2013   *      Korea          11
....

Now I want to do an sum/group by aggregate query that sums up total visit durations by country, but I want to produce only three groups (USA, Canada, Everyone Else)

And produce an ideal output like this:

  country     sum(durations) 
   USA            123456789
   Canada          55555679
Everyone Else        696969

Is it possible to do this in a single query?

My first idea will be to UNION 2 queries : 1 for the USA & Canada and 1 for others... — JoDev
– JoDev, Commented Apr 4, 2013 at 14:58
thats a great idea, but ideally I would like to write it in a single query — Green Demon
– Green Demon, Commented Apr 4, 2013 at 14:59

Ike Walker · Accepted Answer · 2013-04-04 14:59:23Z

3

You can do this with a CASE statement:

select (
  case 
    when country in ('USA','Canada') then country 
    else 'Everyone Else' 
   end
) as country_group,
  sum(duration)
from your_table
group by country_group

answered Apr 4, 2013 at 14:59

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

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

Comments

Adriano Carneiro · Accepted Answer · 2013-04-04 14:58:01Z

1

select country, sum(duration)
from
(
select date, ip, 
       case 
           when country <> 'USA' and country <> 'Canada' then 'Everyone Else'
           else country
       end country,
       duration
from trafficlog
) subsel
group by country

answered Apr 4, 2013 at 14:58

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

3 Comments

Ike Walker Over a year ago

This will give correct results, but the sub-query is unnecessary and makes the query slower than it needs to be. See my answer to this question for an example of how to do this without a sub-query.

Adriano Carneiro Over a year ago

@IkeWalker Thanks for the input. Even though this is a MySQL question, I try to post SQL Ansi answers whenever I can, so if another person has the same issue in other DB, they can try the solution. That said, MySQL accepts aliases in Group by clause, others DBs, not so much.

Ike Walker Over a year ago

I can appreciate that. If I were implementing this on an RDBBMS that doesn't support column aliases in the GROUP BY, then I would just repeat the entire CASE statement in the GROUP BY to avoid sacrificing efficiency.

Marc B · Accepted Answer · 2013-04-04 14:58:31Z

1

Something like

SELECT ..
...
GROUP BY
    CASE country
        WHEN 'USA' THEN 'USA'
        WHEN 'Canada' THEN 'Canada'
        ELSE 'Everyone Else'
    END CASE

would do the trick. going off the top of my head, so this probably won't work as is. But MySQL does accept arbitrary logic for group/where/order clauses, so you can write whatever you want to do the grouping.

Another option:

SELECT IF(country NOT IN ('Canada', 'USA'), 'Everyone Else', country) AS country
...
GROUP BY country

answered Apr 4, 2013 at 14:58

Marc B

362k44 gold badges433 silver badges508 bronze badges

1 Comment

Green Demon Over a year ago

great to know you can put in arbitrary group by logic. will make my life much easier

Collectives™ on Stack Overflow

combining groups in aggregate GROUP BY functions in MySQL

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related