英文:
oracle query not getting count as 0 with group by and count query , instead getting empty row
问题
这是查询，但我们没有位置# =83的数据，但我想要输出为cnt=0
SELECT
COUNT(1) AS cnt , location# as record_no
FROM
test
WHERE
cny# = 1
AND location# = 83 group by location#
获得的输出是一个空行，而不是期望的输出，类似于这样
cny
---|-----
1 0
使用下面的查询，我获得了期望的输出
SELECT
COUNT(1) AS cnt
FROM
test
WHERE
cny# = 1
AND location# =83
如何能够获得与上述期望输出相同的结果，同时使用group by并在选择中添加列，就像我在上面的查询中所示？
英文:
This is the query and we dont have data for location# =83 but i want output as cnt=0
SELECT
COUNT(1) AS cnt , location# as record_no
FROM
test
WHERE
cny# = 1
AND location# = 83 group by location#
getting the output as empty row, instead expected out similar to this like
cny
---|-----
1 0
With below query i m getting the expected output
SELECT
COUNT(1) AS cnt
FROM
test
WHERE
cny# = 1
AND location# =83
How can i get same output as shown in expected above with using group by and adding the column in select as in top query i have shown
答案1
得分: 1
以下是翻译好的代码部分：
SQL> select * from test;
LOCATION# CNY#
---------- ----------
1 1
1 1
83 22
This, as you know, works:
SQL> select count(*)
2 from test
3 where cny# = 1
4 and location# = 83;
COUNT(*)
----------
0
You'd want to see that *zero* along with `location# = 83`, but - there's none:
SQL> select location#, count(*)
2 from test
3 where cny# = 1
4 and location# = 83
5 group by location#;
no rows selected
-----------
One option is to use self-join; just to check what's going on without filter on `location#`:
SQL> select a.location#, count(distinct b.rowid) cnt
2 from test a left join test b on a.location# = b.location# and a.cny# = 1
3 --where a.location# = 83
4 group by a.location#;
LOCATION# CNT
---------- ----------
83 0
1 2
With filter:
SQL> select a.location#, count(distinct b.rowid) cnt
2 from test a left join test b on a.location# = b.location# and a.cny# = 1
3 where a.location# = 83
4 group by a.location#;
LOCATION# CNT
---------- ----------
83 0
希望这有帮助。如果您有任何其他问题，请随时提出。
英文:
Sample data:
SQL> select * from test;
LOCATION# CNY#
---------- ----------
1 1
1 1
83 22
This, as you know, works:
SQL> select count(*)
2 from test
3 where cny# = 1
4 and location# = 83;
COUNT(*)
----------
0
You'd want to see that zero along with location# = 83, but - there's none:
SQL> select location#, count(*)
2 from test
3 where cny# = 1
4 and location# = 83
5 group by location#;
no rows selected
One option is to use self-join; just to check what's going on without filter on location#:
SQL> select a.location#, count(distinct b.rowid) cnt
2 from test a left join test b on a.location# = b.location# and a.cny# = 1
3 --where a.location# = 83
4 group by a.location#;
LOCATION# CNT
---------- ----------
83 0
1 2
With filter:
SQL> select a.location#, count(distinct b.rowid) cnt
2 from test a left join test b on a.location# = b.location# and a.cny# = 1
3 where a.location# = 83
4 group by a.location#;
LOCATION# CNT
---------- ----------
83 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论