Skip to content
Advertisement

Grouping certain rows into one row in sqlite

This is the query that got me this result.

 SELECT
    country,
    COUNT(DISTINCT(customer_id)) total_customers
FROM customer c
GROUP BY 1
ORDER BY 2 DESC

    country   total_customers

1   India       2
2   Portugal    2
3   Argentina   1
4   Australia   1
5   Austria     1
6   Belgium     1
7   Chile       1

How can I group all the 1’s into a category Others?

Desired output: country customers

1   India       2
2   Portugal    2
3   Others      5

Advertisement

Answer

Use case and group by, twice:

select (case when total_customers = 1 then 'Others' else country end) as country,
       sum(total_customers) as customers
from (select country,
             count(distinct customer_id) as total_customers
      from customer c
      group by country
     ) c
group by (case when total_customers = 1 then 'Others' else country end);
1 People found this is helpful
Advertisement