group by子句的where子句也应该返回计数零 [英] group by clause with where clause should return count zero also

问题描述

亲爱的朋友们



SELECT * FROM A

1 A A ACCEPT

2 A ACCEPT

3 C接受

4 C接受

5 B HOLD

6 G HOLD

7 G HOLD

8 B REJECT

9 G REJECT

10 H REJECT

11 H REJECT

12 A NEW

13 H REJECT

14 H NEW

15 C NEW

16 D NEW

17 E NEW

18 D接受

19 D接受

20 F接受

21 I NULL



这是我的桌子。



SELECT DISTINCT（PROD）来自A

A

B

C

D

E

F

G

H

我



这些是我有的产品。



SELECT PROD，ISNULL（COUNT（*），0）来自一个地址='接受'GROUP BY PROD

A 2

C 2

D 2

F 1



我执行这个我得到上述重新sult



但是我的要求：



A 2

B 0

C 2

D 2

E 0

F 0

G 0

H 0

我1



如何实现它。



请帮帮我



提前感谢

Dear friends

SELECT * FROM A
1 A ACCEPT
2 A ACCEPT
3 C ACCEPT
4 C ACCEPT
5 B HOLD
6 G HOLD
7 G HOLD
8 B REJECT
9 G REJECT
10 H REJECT
11 H REJECT
12 A NEW
13 H REJECT
14 H NEW
15 C NEW
16 D NEW
17 E NEW
18 D ACCEPT
19 D ACCEPT
20 F ACCEPT
21 I NULL

This is my table.

SELECT DISTINCT(PROD) FROM A
A
B
C
D
E
F
G
H
I

These are the products i have.

SELECT PROD,ISNULL(COUNT(*),0) FROM A WHERE STATUS='ACCEPT' GROUP BY PROD
A 2
C 2
D 2
F 1

When i execute this i am getting the above result

But my requirement :

A 2
B 0
C 2
D 2
E 0
F 0
G 0
H 0
I 1

How to achieve it.

please help me

thanks in advance

推荐答案

SELECT PROD，ISNULL（ COUNT（*），0）FROM WHERE STATUS ='接受'GROUP BY All PROD

SELECT PROD,ISNULL(COUNT(*),0) FROM A WHERE STATUS='ACCEPT' GROUP BY All PROD

这篇关于group by子句的where子句也应该返回计数零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！