Why would DISTINCT COUNT() return 9 instead of 1?
db2, ibm-midrange, sql
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).
Problem
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.