英文:
how to use sql count() function multiple times in same table with where clause
问题
select
sum(case when terminal_id like '911%' then 1 else 0 end) as 911like,
sum(case when terminal_id like '111%' then 1 else 0 end) as 111like
from table
where created_at between '2022-12-01' and '2022-12-10';
英文:
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 |
答案1
得分: 1
这是使用 count("expression") 完成的。当表达式为真时，将计数。
选择 count(case when terminal_id like '911%'
then name
end) 作为 [911like]
,count(case when terminal_id like '111%'
then name
end) 作为 [111like]
从表中
其中 created_at 在 '2022-12-01' 和 '2022-12-10' 之间。
英文:
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'
答案2
得分: 1
更简化的版本：
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
英文:
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';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论