勝手にテーブルとデータをそれっぽく仮定した回答です。
SQL
1create table A (label char(1),game int);
2insert into A values('A',1);
3insert into A values('A',2);
4insert into A values('A',3);
5insert into A values('A',4);
6insert into A values('A',5);
7insert into A values('A',6);
8insert into A values('A',7);
9insert into A values('A',8);
10insert into A values('A',9);
11insert into A values('B',8);
12insert into A values('B',9);
13insert into A values('C',1);
14insert into A values('D',9);
15insert into A values('E',5);
16insert into A values('E',6);
17insert into A values('E',7);
18insert into A values('E',8);
19insert into A values('F',8);
20insert into A values('F',9);
countで書かれていた結果。
SQL
1select label,count(*) as result from A group by label;
2+-------+--------+
3| label | result |
4+-------+--------+
5| A | 9 |
6| B | 2 |
7| C | 1 |
8| D | 1 |
9| E | 4 |
10| F | 2 |
11+-------+--------+
resultで書かれていた結果。
SQL
1select label,count(*) as result from A where game <= 7 group by label;
2+-------+--------+
3| label | result |
4+-------+--------+
5| A | 7 |
6| C | 1 |
7| E | 3 |
8+-------+--------+
9
導出表+外部結合+COALESCEで実現した場合の例。
SQL
1select label1,coalesce(result,0) as result from (select distinct label as label1 from A) t1 left join (select label as label2,count(*) as result from A where game <= 7 group by label) t2 on label1=label2;
2+--------+--------+
3| label1 | result |
4+--------+--------+
5| A | 7 |
6| B | 0 |
7| C | 1 |
8| D | 0 |
9| E | 3 |
10| F | 0 |
11+--------+--------+
caseで個数に置き換えてsumで計算した場合の例。
SQL
1select label,sum(case when game<= 7 then 1 else 0 end) as result from A group by label;
2
3select label,sum(num) as result from (select label,case when game<= 7 then 1 else 0 end as num from A) t group by label;