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

    please help me

    thanks in advance


    Saturday, January 25, 2014 4:10 AM

Answers

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

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.

    please help me

    thanks in advance



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

    I made the problem into adding new products to an inventory.  

    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