⇧ 結局、お金ってことですかね...
Oracle DatabaseでGROUP BYでCOUNTするとNULLが返ってくるという罠
Oracleさんの公式のドキュメントによりますと、
アスタリスク(*)を指定すると、このファンクションは重複値およびNULL値を含むすべての行を戻します。COUNT
はNULLを戻しません。
https://docs.oracle.com/cd/F19136_01/sqlrf/COUNT.html#GUID-AEF08B79-024D-4E3A-B362-9715FB011776
⇧ とあるのだけど、何故、嘘を書くのか...
実際に、NULLを戻してくれちゃってますけど...
SELECT facility_code, COUNT(*) FROM W_M_FACILITY --WHERE facility_code = '0000000000001' WHERE facility_code = '1000000000001' GROUP BY facility_code;
ネットの情報によると、
⇧ どうも、COUNT(*) 以外にも取得するカラムがある場合は、NULLが返ってくるのを回避する術はないっぽい...
Oracleのコミュニティの回答によると、
When you just use count on it's own (and you should use count(*) not count(1) as the optimiser will re-write it internally to count(*) anyway)... then you are saying that you want a count of all the rows.
When you introduce the group by clause you are saying thay you want a count of records within particular groups... but if there are no groups returned, it can't give you a row for any group with it's equivalent count, because there is not rows for the groups. With grouping, you've have to have at least 1 row per group to get a count for it.
When you introduce the group by clause you are saying thay you want a count of records within particular groups... but if there are no groups returned, it can't give you a row for any group with it's equivalent count, because there is not rows for the groups. With grouping, you've have to have at least 1 row per group to get a count for it.
Marked as Answer by 793965 · Sep 27 2020
https://forums.oracle.com/ords/apexds/post/count-1-returns-null-in-group-by-5691
⇧ 1行も取得できない場合に、GROUP BYで行数が取得できなくて、COUNT(*) がNULLを返すってことらしい...
いや、Oracleさん、それならそれで、COUNTのドキュメントにちゃんと説明入れるべきでしょ...
というわけで、残念ながら、
- SELECT COUNT(*) ...
- SELECT column1, column2 ...
のように2回のSQLを実行せざるを得ないという...
毎度モヤモヤ感が半端ない...