英文:
how to use sql count() function multiple times in same table with where clause
问题
selectsum(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 111likefrom tablewhere 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 tablewherecreated_at between'2022-12-01' and '2022-12-10' andterminal_id like '911%'
select count(name) from tablewherecreated_at between'2022-12-01' and '2022-12-10' andterminal_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 nameend) 作为 [911like],count(case when terminal_id like '111%'then nameend) 作为 [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 nameend) as [911like],count(case when terminal_id like '111%'then nameend) as [111like]from tablewhere 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 111likefrom my_tablewhere 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 111likefrom my_tablewhere created_at between '2022-12-01' and '2022-12-10';
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论