Databases
StackOverflow2013
Postsdbo
POPL/SQL SELECT with multiple COUNT(DISTINCT xxx) - unexpected results
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPL/SQL SELECT with multiple COUNT(DISTINCT xxx) - unexpected results
    primarykey
    data
    text
    <p>I'm trying to put together a query for an Oracle 11g application and I've run into a problem.</p> <p>I'll simplify the real scenario to make it easier to understand (and also to protect the client's data):</p> <ul> <li>Table A is the base table. It has a known identifier in it that I pass in to the query.</li> <li>For each entry in Table A there may be multiple entries in Table B. Table B contains a value that I am interested in.</li> <li>For each entry in Table B there may also be multiple entries in Table C. Table C contains another value I'm interested in.</li> <li>I also have an XML snippet containing a list of values that may or may not match up to the values of interest in table C.</li> <li>The query does an outer join to the XML so that if there is a matching value it will return the value again, otherwise it is null.</li> </ul> <p>What I want to do is get back the identifier I passed in, a count of the unique values in B and C, as well as a count of the unique (and non-null) values from the XML part of the join.</p> <p>My current query is:</p> <pre><code>SELECT a.ID , COUNT(DISTINCT b.VAL) AS B_VAL , COUNT(DISTINCT c.VAL) AS C_VAL , COUNT(DISTINCT xml.VAL) AS XML_VAL FROM a, b, c, XMLTABLE('/field1/collection/value' passing my_xml_type COLUMNS VAL VARCHAR2(50) PATH '.') xml WHERE a.ID = b.SOME_ID AND b.OTHER_ID = c.OTHER_ID AND c.VAL = xml.VAL (+) </code></pre> <p>Now if you forget about the counting and just return rows, an example result set might look something like this:</p> <pre><code>ID B_VAL C_VAL XML_VAL --------------------------------------- X abc 123 123 X abc 456 null X abc 789 789 X abc 789 789 </code></pre> <p><strong>DESIRED:</strong> Now when I want to do the distinct counts, I'd like it to return:</p> <pre><code>ID B_VAL C_VAL XML_VAL --------------------------------------- X 1 3 2 </code></pre> <p><strong>ACTUAL:</strong> However, this is what I'm getting when I have them all as COUNT(DISTINCT ...):</p> <pre><code>ID B_VAL C_VAL XML_VAL --------------------------------------- X 1 1 1 </code></pre> <p><strong>ALTERNATIVE:</strong> ...and if I take the DISTINCT out of the counts then I get:</p> <pre><code>ID B_VAL C_VAL XML_VAL --------------------------------------- X 1 4 3 </code></pre> <p>How come the DISTINCT seems to be operating only within a particular B_VAL, but taking it out causes it to operate across all the rows but not taking uniqueness into account?</p> <p>Is there another way of doing this that doesn't involve having to replicate all the joins as a sub-query? Have I missed the point entirely?</p> <p>(Please note, I'm not a DB developer at all, I've just been pulled in to help out, so sorry if this is an easy problem... I HAVE searched Google and browsed this site for answers before posting, though!)</p> <p>Thanks.</p> <hr> <p>I've found that if I take the XML table join out then the count distinct works OK across the B_VAL and C_VAL... So perhaps it's something weird with how Oracle handles XML table joins? </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAPC
    1. USChrisC
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPL/SQL SELECT with multiple COUNT(DISTINCT xxx) - unexpected results
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPL/SQL SELECT with multiple COUNT(DISTINCT xxx) - unexpected results
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POPL/SQL SELECT with multiple COUNT(DISTINCT xxx) - unexpected results
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONote, I've found that if I take the XML table join out then the count distinct works OK across the B_VAL and C_VAL... perhaps something weird with how Oracle handles XML table joins?
      singulars
      1. POPL/SQL SELECT with multiple COUNT(DISTINCT xxx) - unexpected results
      1. USChrisC
    2. COGiven the answers below, what version of Oracle are you using? And does the test case given by Vincent also fail for you?
      singulars
      1. POPL/SQL SELECT with multiple COUNT(DISTINCT xxx) - unexpected results
      1. USMike Meyers
    3. CO@MikeyByCrikey, I'm running 11.2.0.1 locally at the moment, and seeing different results to Vincent. Unfortunately even the nested query doesn't work for me. Besides, I need to find out the version of the target production database, I can't just patch my dev database...
      singulars
      1. POPL/SQL SELECT with multiple COUNT(DISTINCT xxx) - unexpected results
      1. USChrisC
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload