# group by clause with where clause should return count zero also

### Question

• 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.

Saturday, January 25, 2014 4:10 AM

• ```SELECT t1.PROD, ISNULL(t2.cnt,0)  cnt
FROM (SELECT DISTINCT(PROD) PROD FROM A) t1
Left JOIN (SELECT PROD,COUNT(*) cnt FROM A WHERE STATUS='ACCEPT' GROUP BY PROD) t2 On t1.PROD=t2.PROD```

Saturday, January 25, 2014 4:23 AM
• ```SELECT PROD,SUM(CASE WHEN status='ACCEPT'  Then 1 Else 0 End )  cnt FROM A
GROUP BY PROD```

Saturday, January 25, 2014 4:30 AM

### All replies

• 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.

Saturday, January 25, 2014 4:16 AM
• ```SELECT t1.PROD, ISNULL(t2.cnt,0)  cnt
FROM (SELECT DISTINCT(PROD) PROD FROM A) t1
Left JOIN (SELECT PROD,COUNT(*) cnt FROM A WHERE STATUS='ACCEPT' GROUP BY PROD) t2 On t1.PROD=t2.PROD```

Saturday, January 25, 2014 4:23 AM
• ```SELECT PROD,SUM(CASE WHEN status='ACCEPT'  Then 1 Else 0 End )  cnt FROM A
GROUP BY PROD```

Saturday, January 25, 2014 4:30 AM
• Thanks you very much

its very helps to me

Thanks a lot

Saturday, January 25, 2014 4:35 AM
• Thanks you very much

its very helps to me

Thanks a lot

Saturday, January 25, 2014 4:35 AM
• Thanks you very much

its very helps to me

Thanks a lot

Saturday, January 25, 2014 4:35 AM
• Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you failed completely). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

This is minimal polite behavior on SQL forums. Because you are rude, we have to type your data to test anything we do for you. Here is a correction for the DDL you did not even bother to try to post.

DROP TABLE Inventory;
CREATE TABLE Inventory
(inventory_ticket INTEGER NOT NULL PRIMARY KEY,
product_id CHAR(1)NOT NULL,
stocking_status CHAR(6)
CHECK(stocking_status IN ('accept', 'hold', 'reject', 'new', '??')));

Why was stocking_status allowed to be NULL? It makes no sense in a data model, so I replaced it with '??' until you can correct this. Se how a corret CHECK() constraint preserves data integrity?  This is the natural versus generated NULL problem and you can Google it.

INSERT INTO Inventory
VALUES
(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'), --  you missed this row in your posting
(21, 'i', '??'); --- NULL is absurd!

WITH All_Products(product_id)
AS
(SELECT DISTINCT product_id FROM Inventory),
Full_Inventory
AS
(SELECT P.product_id, I.stocking_status
FROM All_Products AS P
LEFT OUTER JOIN
Inventory AS I
ON I.product_id = P.product_id
AND I.stocking_status IN ('accept', '??')
)

SELECT product_id, COUNT(stocking_status) AS inventory_level
FROM Full_Inventory
GROUP BY product_id;

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Saturday, January 25, 2014 6:50 PM