Sql conditional account

Question

Can someone help me with sql and sqlite query for the following condition. To be specific, I wanted to show a combined number of 2 categories.

**Table**
Category    Sub_category
  A              1
  A              2
  A              2
  B              1
  C              1
  C              1
  C              2
  D              1
  D              1
  D              1
  D              2
  D              3
**Required Output**
    Category    Sub_category    Count   **condition(not part of o/p just instruction)**
        A             1           1        -
        A             2+          2        -    
        B             1           1        -    
        C             1           2        -
        C             2+          1        -
        D             1           3        -    
        D             2+          1 should contain the count of 2 and more

I can achieve the same use below:

  select Category,
         count(CASE WHEN Sub_category = 1 THEN Sub_category END) AS '1',
         count(CASE WHEN Sub_category >= 2 THEN Sub_category END) AS '2+'
  from table

however, the output is a little different, so it only searches for the first output.

**Output**
Category    1   2+
   A        1   2
   B        1   0
   C        1   2
   D        3   2

Thanks in advance!

Answer 1

Do you want to:

select Category,
       (case when sub_category = 1 then '1' else '2+' end) as sub_category,
       count(*)
from table
group by Category,
         (case when sub_category = 1 then '1' else '2+' end);

That is, you want to put groups of subcategories in rows, not in columns.

Answer 2

You just miss one column. Add the last counter without filtering to get the total:

select Category,
     count(CASE WHEN Sub_category = 1 THEN Sub_category END) AS '1',
     count(CASE WHEN Sub_category >= 2 THEN Sub_category END) AS '2+',
     count(*)
   from table
   group by Category, count(CASE WHEN Sub_category = 1 THEN Sub_category END), count(CASE WHEN Sub_category >= 2 THEN Sub_category END), count(*)