I am doing a python project that needs to use SQLITE queries and I'd like to have a specific output for this query, from what I've searched so far on the internet I'm almost sure that it's either gonna be in a temporary table or something in two parts so here we go

lets say I have this table lets call it "enterprise" that has a "JuridicalForm" column and others column that we don't really need also let's add a count for each of these columns.

Now let's add a condition because I don't want the forms that are less than 100

SELECT JuridicalForm AS 'form', COUNT(JuridicalForm) AS 'count' FROM enterprise GROUP BY form HAVING count > 99

Now this is great and all but then I would like the columns that are less than 100 to be added as a sum and called "others":

I searched online for solutions with unions, joins and also some select query with another select condition but doesn't seem to work and I'd appreciate some help or even some hints if you don't want me to just copy pasta without understanding. If it is impossible I'll just do it in 2 parts and use pandas dataframe to merge them somehow but I'll still need to figure out how to get the sum of the count of "form" in one row.

CodePudding user response：

Use 2 levels of aggregation:

SELECT form,
       SUM(count) count
FROM (
  SELECT CASE WHEN COUNT(*) > 99 THEN JuridicalForm ELSE 'others' END form, 
         COUNT(*) count 
  FROM enterprise 
  GROUP BY JuridicalForm
)
GROUP BY form
ORDER BY form = 'others', count;