SQLite: Selecting multiple counts from one column

I am running a query with SQLite and am relatively new to this. Glance of my dataset:

  ColA       ColB 
 Atlanta     mild
 Atlanta    moderate
 Miami       high
 Houston    moderate
 Houston    moderate
 Miami       mild
 Atlanta    moderate

I want group the results by Col A and then show 3 more columns (mild, moderate, high) and their counts — Here is what I’m looking for:

 ColA    mild    moderate    high
Atlanta   1        2          0
Houston   0        2          0
Miami     1        0          1

I know how I can select just one of the columns (say, mild), but not sure how to select the others. Thanks!

Answer

You can pivot with conditional aggregation:

select 
    colA,
    sum(case when ColB = 'mild' then 1 else 0 end) mild,
    sum(case when ColB = 'moderate' then 1 else 0 end) moderate,
    sum(case when ColB = 'high' then 1 else 0 end) high
from mytable
group by colA

Demo on DB Fiddle:

ColA    | mild | moderate | high
:------ | :--- | :------- | :---
Atlanta | 1    | 2        | 0   
Houston | 0    | 2        | 0   
Miami   | 1    | 0        | 1