DB2 SQL: How to 'count' the amount of records returned by a having clause

Question

Table XRDK/WHSHIPP_R3 has 4 columns - ZNWHSE, ZNSITE, ZNMANE, ZNRECD with a total of 1,071 records.

I have isolated the ZNMANE numbers that have been used more than once by;

SELECT ZNMANE FROM XRDK/WHSHIPP_R3
GROUP BY ZNMANE                   
HAVING (COUNT(ZNMANE) >1)         
ORDER BY 1

I would like a total count of these isolated records, but if I change it to;

SELECT COUNT(ZNMANE) FROM XRDK/WHSHIPP_R3
GROUP BY ZNMANE                   
HAVING (COUNT(ZNMANE) >1)         
ORDER BY 1

I just get a load of 2s which must be the individual count for each ZNMANE record.

I tried this;

SELECT ZNMANE FROM XRDK/WHSHIPP_R3
GROUP BY ZNMANE                   
HAVING (COUNT(ZNMANE) >1)         
ORDER BY 1                        
UNION ALL
SELECT COUNT(ZNMANE) FROM XRDK/WHSHIPP_R3

But this returned 1071 at the top, so I guess it just counted the whole file. Any ideas?

Answer 1

On method is a subquery:

SELECT COUNT(*)
FROM (SELECT ZNMANE
      FROM XRDK/WHSHIPP_R3
      GROUP BY ZNMANE                   
      HAVING COUNT(ZNMANE) > 1     
     ) z 

If you want the value in each row, use window functions:

      SELECT ZNMANE, COUNT(*) OVER () as NumTotal
      FROM XRDK/WHSHIPP_R3
      GROUP BY ZNMANE                   
      HAVING COUNT(ZNMANE) > 1