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.

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:

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.

Would that work, I wonder:

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

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

Would that work, I wonder:

select count(distinct grp) from GrpTbl

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.

This will not produce the correct results
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.

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

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.