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 |
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'
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';