Am Getting Counts Only if > 0 But Need All Counts Including 0

[Coder7]

Hi All

I'd really appreciate your assistance! I have to differentiate between (category 1): grp_id where at least one row for a given grp_id has a cob_ind > spaces and (category 2 grp_id where all rows for a given grp_id has cob_ind = spaces. I'm using count to do this. If count > 0 it fits category 1. If count = 0 it fits category 2.

I'm trying to get a count for each grp_id in TABLE A of how many total rows have cob_ind > ' ' (space). I'm using 2 queries. The first query works fine but the second query returns 0 rows and there are grp_ids in TABLE A where cob_ind for all rows for a particular grp_id = spaces.

After the 2 queries below, I've displayed a dummy table and what the result tables should look like for query 1 and query 2.
------------------
TO GET GRP_IDS where at least one row has a cob_ind value (this works):
<begin sql>
Select grp_id, count(grp_id) as grpcount
from a_iogbxf x
where x.cob_ind > ' '
group by grp_id having grpcount > 0
</end sql>
------------
TO GET GRP_IDS where at all cob_ind = spaces (this does not work):
<begin sql>
Select grp_id, count(grp_id) as grpcount
from a_iogbxf x
where x.cob_ind > ' '
group by grp_id having grpcount = 0
</end sql>
------------

Source TABLE A data:
grp_id cob_ind
0001
0001 444
0002
0002
0003 888

result set query 1
grp_id grpcount
0001 1
0003 1

result set for query 2 should be:
grp_id grpcount
0002 0

Thanks for any and all help! I've been wrestling with this all morning.

 

[MarcLodge]

Coder7,
I'm not sure I understand your query, alongside your expected results. In the results for the 2nd query, you say you want to see

grp_id grpcount
0002 0

Surely that should be

grp_id grpcount
0002 2

as there are two rows under the grp_id of 0002 with a cob_ind of spaces.

If I understand what you're after then the following might help:
SELECT A.GRP_ID, COUNT(*)
FROM A_IOGBXF A
WHERE A.COB_IND = ' '
AND NOT EXISTS (SELECT ' '
FROM A_IOGBXF B
WHERE A.GRP_ID = B.GRP_ID
AND B.COB_IND <> ' ')
GROUP BY A.GRP_ID

 

[stevexff]

Is cob_ind nullable? In other words, are they really spaces, or null? If it is null, then you might get a result by changing the where clause in the second query to

where x.cob_ind is null

Alternatively you could try

select grp_id, value_set, count(*)
   from (
      select grp_id,
         case when cob_ind > '' then 'non-blank' else 'blank' end as value_set
      from a_iogbxf) as temp
   group by grp_id, value_set
   order by grp_id

which should give you the result you want in a single query.

 

[Coder7]

Cob_ind is not nullable. And Marc, you're right - I want to see 2 as the count not zero.

Thank you both very much. Since the column is not nullable I'll try Marc's suggestion.

Hope you had a great weekend and thanks, again!

 

[MarcLodge]

Good luck. Let us know how you get on.