CODEWITHSUNDEEP
sqlpostgresql
Antonio
Antonio

Reputation: 134

How to group different fields from a column into categories when summing up column values in SQL?

Table: 

+----------+----------+
| os       | clicks   |
+----------+----------+
| iOS      | 700      |   
| iOS      | 200      |    
| Android  | 100      |  
| Ubuntu   | 300      | 
| Windows  | 100      | 
| Ubuntu   | 100      | 
+----------+----------+

I want to sum the clicks column and group the fields from os into two categories, like so:

+----------+----------+
| os       | clicks   |
+----------+----------+
| phone    | 1000     |   
| computer | 500      | 
+----------+----------+

where 'phone' comes from 'iOS' + 'Android', and 'computer' from 'Windows' + 'Ubuntu'.

How can I achieve this in a single query?

Upvotes: 1

Views: 36

Answers (1)

user330315
user330315

Reputation:

You can aggregation on an expression:

select case 
          when os in ('iOS', 'Android') then 'phone'
          when os in ('Windows', 'Ubuntu') then 'computer'
       end as os_type,
       sum(clicks)
from the_table
group by os_type;

Upvotes: 2

Related Questions