CODEWITHSUNDEEP
mysqlsqlaggregate
user8131108
user8131108

Reputation: 21

aggregate one column to different fields according to another column

I have a table like

id country   os         sales
1  us        android    100
2  us        ios        50

I want to aggregate them to one record like

id country android_sales ios_sales
1  us      100           50

I have simplified the situation, so using max or min function may not be accurate, only based on string comparing. I have a solution like 

select sum((os='android')*sales) as android_sales, 
       sum((os='ios')*sales) as ios_sales 
group by country

but I feel like it is too complicated, is there a better solution?

Upvotes: 1

Views: 70

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do conditional aggregation :

select min(id), country, 
       sum(case when os = 'android' then sales else 0 end) as android_sales,
       sum(case when os = 'ios' then sales else 0 end) as ios_sales
from table t
group by country;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use conditional aggregation . . . a case inside a sum():

select country,
       sum(case when os = 'android' then sales else 0 end) as sales_android,
       sum(case when os = 'ios' then sales else 0 end) as sales_ios
from t
group by country;

The id is probably not needed for your results. If you do want an id from the table, then add min(id).

Upvotes: 1

Related Questions