CODEWITHSUNDEEP
mysqlsqlaggregate-functions
Green Demon
Green Demon

Reputation: 4208

combining groups in aggregate GROUP BY functions in MySQL

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?

Upvotes: 0

Views: 1637

Answers (3)

Ike Walker
Ike Walker

Reputation: 65547

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

Upvotes: 3

Marc B
Marc B

Reputation: 360662

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

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58615

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

Upvotes: 1

Related Questions