CODEWITHSUNDEEP
sqloracle-databasemergegroup-bysum
theb
theb

Reputation: 117

Query to merge (some) rows

I'm currently trying to create a query that merges some rows to one category and sums up the related values.

I'll try to illustrate it with the following example: 

Country | Sales Jan | Sales Feb | Sales Mar
--------+-----------+-----------+----------
Austria |    100    |    110    |    120
Spain   |    120    |    130    |    140
Italy   |     70    |     50    |     70
Japan   |     10    |     10    |     10
Brazil  |     20    |     20    |     20
Mexico  |      5    |      5    |      5

The query should show the data for every European country and merge all other rows to the category "Other" and summarize the related values.

The result should look like this:

Country | Sales Jan | Sales Feb | Sales Mar
--------+-----------+-----------+----------
Austria |    100    |    110    |    120
Spain   |    120    |    130    |    140
Italy   |     70    |     50    |     70
Other   |     35    |     35    |     35

Update: The countries that should be shown in detail are specified in a list of strings (in this case, the list would contain "Austria", "Spain", "Italy"). So every country which is not contained in this list should be merged to category "Other" by the query.

Upvotes: 1

Views: 56

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Try this:

select case when country in('Austria','Spain','Italy')
            then Country else 'Other' end as Country,
       sum(Jan) as Jan,
       sum(Feb) as Feb,
       sum(Mar) as Mar

from t
group by case when country in('Austria','Spain','Italy')
              then Country else 'Other' end

Fiddle http://sqlfiddle.com/#!4/93719/2

Upvotes: 3

Related Questions