Results 1 to 10 of 10

Thread: DB2 Row Counts

  1. #1
    Join Date
    Apr 2008
    Posts
    8

    Unanswered: DB2 Row Counts

    Hello,

    I have a DB2 (Mainframe) table called 'GRPTBL' having rows/columns as below;

    GRP SUBGRP

    CCC C02
    AAA A01
    BBB B02
    BBB B01
    AAA A03
    CCC C01
    BBB B02
    CCC C02
    AAA A03
    AAA A02

    My query to find out the number of different 'GRP's looks like

    SELECT count(*)
    into wk-grp-cnt
    FROM grpTbl
    WHERE grp in (
    select grp
    FROM testTbl
    GROUP BY GRP)

    returns a value of 10 to the wk-grp-cnt instead of 3.

    I do appreciate any help to adjust my query to get the expected results.

    Thanks in advance.

    Natasha.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In the outer select, you select all rows from table GRPTBL that have a value in the GRP column, which can also be found in TESTTBL. You have a GROUP BY in the subselect on TESTTBL, and that GROUP BY is useless because you don't have to group anything there. Since all rows qualify in the outer select, you will get a count of how many rows are in the table.

    What you probably want to have is:
    Code:
    SELECT COUNT(*)
    INTO   :wk-grp-cnt
    FROM   ( SELECT DISTINCT grp
             FROM grptbl )
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Apr 2008
    Posts
    8

    Correction

    Thanks for your response...

    There is a small correction to my initial query;

    SELECT count(*)
    into wk-grp-cnt
    FROM grpTbl
    WHERE grp in (
    select grp
    FROM grpTbl
    GROUP BY GRP)

    I tried your solution, but getting some syntax error;

    Could you please take a look at it... Thanks a lot.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Would that work, I wonder:
    Code:
    select count(distinct grp) from GrpTbl
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I think you should try something like this:

    SELECT count(*)
    into wk-grp-cnt
    FROM grpTbl
    WHERE grp in (
    select distinct grp
    FROM grpTbl)
    GROUP BY grp

  6. #6
    Join Date
    Apr 2008
    Posts
    8

    Syntax errors...

    This is not working... anyway thanks for the reply.

    Would that work, I wonder:

    select count(distinct grp) from GrpTbl

  7. #7
    Join Date
    Apr 2008
    Posts
    8

    Getting SqlCode of -811...

    Tried your updated query.. Unfortunately getting -811 because the subquery is returning more than 1 row ...(3 rows in this case).

    Any other solution... ? Thank you ..- N.


    Quote Originally Posted by aflorin27
    I think you should try something like this:

    SELECT count(*)
    into wk-grp-cnt
    FROM grpTbl
    WHERE grp in (
    select distinct grp
    FROM grpTbl)
    GROUP BY grp

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    This will not produce the correct results
    Code:
    SELECT COUNT(*) FROM ... WHERE grp IN ( ... ) GROUP BY ...
    because the count will be computed for each group and you don't get a count of groups.

    My query had the problem that it didn't give the temp table a name. Adding a "AS t" at the end will do the trick.

    And n_i's approach is the easiest and most straight-forward.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Now I saw that you updated the initial query.
    In this case, you should try:

    SELECT count(distinct grp)
    into wk-grp-cnt
    FROM grpTbl

  10. #10
    Join Date
    Apr 2008
    Posts
    8

    n_i's solution is working !

    n_i's solution is giving the correct results on mainframe.. Initially I tried it
    using Ms-Access and was getting some syntax errors...

    Anyways... Thanks to all of you guys.. I really appreciate your response and support... ! - N.


    Quote Originally Posted by stolze
    This will not produce the correct results
    Code:
    SELECT COUNT(*) FROM ... WHERE grp IN ( ... ) GROUP BY ...
    because the count will be computed for each group and you don't get a count of groups.

    My query had the problem that it didn't give the temp table a name. Adding a "AS t" at the end will do the trick.

    And n_i's approach is the easiest and most straight-forward.

Posting Permissions

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