Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2016
    Posts
    3

    Unanswered: DB2 Count for non matching rows

    I need to get the count of rows even when they are not matching. I need to look around 1500 codes (in the IN CLAUSE) and need to identify which one doesn't have any data in that table.


    TABLE 1

    Col1 Col2
    D1234 ABCD
    D1235 ABCD
    D1237 ABCD
    D1237 ABCD
    D1210 ABCD



    SELECT COL1, COUNT(COL2)
    FROM TABLE1
    WHERE COL1 IN ('D1234',
    'D1235',
    'D1236',
    'D1237',
    'D1238',
    'D1239',
    'D1210'
    )
    GROUP BY COL1


    Want the below output

    D1234 1
    D1235 1
    D1236 0
    D1237 2
    D1238 0
    D1239 0
    D1210 1

    How can I get the about result or identify the codes which doesn't have any data in TABLE1.

    Thanks for your help

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    112
    Provided Answers: 13
    Code:
    select v.col1, count(t.col1) cnt
    from table(values 
    'D1234',
    'D1235',
    'D1236',
    'D1237',
    'D1238',
    'D1239',
    'D1210'
    ) v(col1)
    left join table (values
      ('D1234', 'ABCD')
    , ('D1235', 'ABCD')
    , ('D1237', 'ABCD')
    , ('D1237', 'ABCD')
    , ('D1210', 'ABCD')
    ) t (Col1, Col2) on v.col1=t.col1
    group by v.col1
    Regards,
    Mark.

  3. #3
    Join Date
    Oct 2007
    Posts
    55
    Provided Answers: 3
    If you are only interested in the ones that do not have data, I would skip the count and turn the LEFT join into a NOT exists subselect.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •