ちょっとgroup byと集約関数と仲良くする必要があったので練習してみた記録です。SQL FiddleのMS SQL Server 2017を使います。

その１

DDL

create table t (
  col1 int 
  ,col2 int
  ,col3 int
  ,col4 int
 )

insert into t values 
(1,1,1,0)
,(1,1,1,0)
,(1,1,1,0)
,(1,1,2,1)
,(1,1,2,0)
,(1,1,2,0)
,(1,1,3,0)
,(1,1,3,0)
,(1,1,3,0)
,(4,5,6,1)
,(4,5,6,-1)
,(5,5,6,1)
,(5,5,6,1)
,(6,5,6,0)
,(6,5,6,null)

DML

-- --------------------------------------------------------------------------------
-- http://mickindex.sakura.ne.jp/database/celko/celko_tis.html
-- col1、col2、col3 の三列でグループ化したときに
-- col4 がすべて 0 であるような行を一意に取得せよ。
-- --------------------------------------------------------------------------------
select 
  col1,col2,col3
from 
  t
group by 
  col1,col2,col3
having 
  max(col4) = min(col4) -- group 内全てのcol4が同一な値である
  and min(col4) = 0 -- 最小値が0である
  and count(*) = count(col4)  -- col4はnullを含んでいない
;

補足

最小値の抑え込み

  and min(col4) = 0 -- 最小値が0である

がないと、

,(5,5,6,1)
,(5,5,6,1)
,(6,5,6,0)
,(6,5,6,null)

が抽出されてしまう。

nullと集約関数

は中身がnullだとエラーになったり省略されたりする*1。なので、ISNULLやcoalesceでnullを抑え込むのもあり。

その２

count(*)とcount(X)とcount(distinct(X))の違いをみてみます。なお、count(distinct(X))は奥が深い問題*2のようです。

DDL

create table t(
  grp char(1) null 
   , val nvarchar(10) null
)

insert into t values 
('A',null)
,('B','bbb')
,('B','bbb')
,('B','ccc')
,('C','ddd')
,('C','eee')
,('D','fff')
,('D',null)
,('D',null)

DML

select
  grp
  ,max(val) as max_val
  ,min(val) as min_val
  ,count(*) as count_aster
  ,count(val) as count_val
  ,count(distinct(val)) as count_distinct_val
from t
  group by grp