This discussion is archived
2 Replies Latest reply: Feb 22, 2013 12:11 PM by Frank Kulash RSS

Using DISTINCT and COUNT together.

bostonmacosx Newbie
Currently Being Moderated
So I have a query which I dicovered is not quite working as planned.
SELECT DISTINCT NULL LINK, 
       TO_CHAR(MONTHS_,'YYYY-MM') DATIME,
       sum(decode(CMS.CMS_NODE_OS.OS_TYPE,'Linux','1',0))"Linux" FROM

  ( SELECT ADD_MONTHS(TRUNC(to_date('28-FEB-2013','DD-MON-YYYY'),'MM'),-ROWNUM + 1) MONTHS_ FROM DUAL CONNECT BY LEVEL <= 
  (SELECT CEIL( MONTHS_BETWEEN(to_date('28-FEB-2013','DD-MON-YYYY'),TO_DATE('01-FEB-2013','DD-MON-YYYY'))) FROM DUAL) )
 THE_TIMELINE
  LEFT JOIN CMS.CMS_NODE ON ( MONTHS_ BETWEEN CREATE_DT AND LAST_DAY(NVL(RETIRE_DT,MONTHS_)))
  LEFT JOIN CMS.CMS_NODE_OS ON CMS.CMS_NODE.NODE_NAME=CMS.CMS_NODE_OS.NODE_NAME
  WHERE CMS.CMS_NODE.NODE_ENV<>'Alias' and CMS.CMS_NODE_OS.OS_TYPE='Linux' GROUP BY (TO_CHAR(MONTHS_,'YYYY-MM')) ORDER BY DATIME
The issue that I'm having is that on the LEFT JOIN of the CMS_NODE_OS table that there a mulitple join records. I just want to have one joined. because of the GROUP and SUM the DISTINCT is rendered null and void.(returns 961) when it should be like (737) records.

How can I just link one CMS_NODE_OS record per CMS_NODE record...
  • 1. Chasm Trap
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    bostonmacosx wrote:
    ... The issue that I'm having is that on the LEFT JOIN of the CMS_NODE_OS table that there a mulitple join records. I just want to have one joined. because of the GROUP and SUM the DISTINCT is rendered null and void.(returns 961) when it should be like (737) records.
    That sounds like a Chasm Trap .
    How can I just link one CMS_NODE_OS record per CMS_NODE record...
    Which one?
    If you can write an ORDER BY clause that would put that row first, then you can use the analytic ROW_NUMBER function. Instead of joining to CMS_NODE_OS, join to a sub-query that has the relevant columns from CMS_NODE_OS, as well as ROW_NUMBER (let's call that column r_num). Include "AND r_num = 1" in the join condition.

    That's just one way to deal with a chasm trap. Depending on your data and your requirements, there may be better ways.



     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
    You'll get better answers faster if you always supply this information whenever you post a question.
  • 2. Re: Chasm Trap
    bostonmacosx Newbie
    Currently Being Moderated
    HI Frank....about 2 minutes before your post I came up with this. I think this solves my issues with the 1 to many join being a 1 to 1 in the end.
    SELECT DISTINCT NULL LINK, 
           TO_CHAR(MONTHS_,'YYYY-MM') DATIME,sum(decode(OS_TYPE,'Linux','1',0))"Linux" FROM
      ( SELECT ADD_MONTHS(TRUNC(to_date('28-FEB-2013','DD-MON-YYYY'),'MM'),-ROWNUM + 1) MONTHS_ FROM DUAL CONNECT BY LEVEL <= (SELECT CEIL( MONTHS_BETWEEN(to_date('28-FEB-2013','DD-MON-YYYY'),TO_DATE('01-FEB-2013','DD-MON-YYYY'))) FROM DUAL) 
      ) THE_TIMELINE
      LEFT JOIN CMS.CMS_NODE ON ( MONTHS_ BETWEEN CREATE_DT AND LAST_DAY(NVL(RETIRE_DT,MONTHS_)))
        LEFT JOIN (SELECT DISTINCT OS_TYPE,NODE_NAME FROM CMS.CMS_NODE_OS) NODEOS ON CMS.CMS_NODE.NODE_NAME=NODEOS.NODE_NAME
        WHERE CMS.CMS_NODE.NODE_ENV<>'Alias' GROUP BY (TO_CHAR(MONTHS_,'YYYY-MM')) ORDER BY DATIME
    What is did was take the LEFT JOIN and added a subquery which only returns 1 row per node name....therefore I think I'm good.
    thanks appreciated always.
    I feel like I'm starting to learn a bit about SQL.
    ROb

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points