Search code examples
sqldb2ibm-midrange

Why would DISTINCT COUNT() return 9 instead of 1?

I have the following statement:

SELECT DISTINCT COUNT(Z.TITLE) AS COUNT 
FROM QMFILES.MPRLRREQDP Y, 
     QMFILES.MPRLRTYPP Z
WHERE Y.REQUEST_TYPE = Z.ID 
  AND Y.REQUEST_ID = 13033;

On this particular result set, if I removed DISTINCT and COUNT() the result set will return nine rows of the exact same data. If I add DISTINCT, I get one row. Adding COUNT() I get a result of nine where I am expecting one. I am assuming the order of operations are affecting my result, but how can I fix this so I get the result I want?

NOTE: This is a subselect within a larger SQL statement.

Solution

  • SELECT DISTINCT COUNT(Z.TITLE) counts the number of rows with a value for Z.TITLE (nine). The DISTINCT is superfluous, since the COUNT has already aggregated the rows.

    SELECT COUNT(DISTINCT Z.TITLE) counts the number of distinct values in Z.TITLE (one).