how to use sql count() function multiple times in same table with where clause

Question

Let's say I have a table:

name	number	date
name1	91104	'2022-12-01'
name2	11161	'2022-12-02'

I am writing these queries:

select count(name) from table
    where
        created_at between
            '2022-12-01' and '2022-12-10' and
        terminal_id like '911%'

select count(name) from table
    where
        created_at between
            '2022-12-01' and '2022-12-10' and
        terminal_id like '111%'

How to write query to get this output:

911like	111like
10	25

Answer 1

This is done using count("expression"). This will count when the expression is true

select count(case when terminal_id like '911%'  
                  then name
               end)  as [911like]
       ,count(case when terminal_id like '111%'  
                  then name
               end)  as [111like]
   from table
 where created_at between '2022-12-01' and '2022-12-10'
     

Answer 2

More simplified:

select sum(terminal_id like '911%') as 911like,
       sum(terminal_id like '111%') as 111like
from my_table
where created_at between '2022-12-01' and '2022-12-10';

https://dbfiddle.uk/PqdCP0Fq