Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: I have this, I want THIS!

    I have gotten this far and now what?
    I have this output ...
    PHP Code:
    CRITICAL        MINOR
    =============== ===============
    6356            6356
    6384
                    6100
                    6049
                    6463 
    I want to count all criticals and also count all minors that do not match a critical?

    So the output I want would be:
    PHP Code:
    CRITICAL        MINOR
    =============== ===============
    2               3 
    since there are 4 total minors but only 3 distinct minors as compared to majors.

    if you really want to see my stupid code:
    PHP Code:
    SELECT criticalminor FROM
    (SELECT DECODE(a.cust_id,NULLb.cust_ida.cust_idcritical FROM (
      (
    SELECT  f.cust_id FROM furnace_issue fnetwork_issue nn
       WHERE f
    .org_id 'FT' AND f.org_id nn.org_id AND
             
    f.component_cd nn.component_cd AND f.issue_cd nn.issue_cd AND
       
    f.active_ind 'Y' AND nn.severity_cd 'C')) a
    FULL outer join
      
    (SELECT  f.cust_id FROM network_issue_log fnetwork_issue nn
       WHERE f
    .org_id 'FT' AND f.org_id nn.org_id AND f.component_cd nn.component_cd AND
             
    f.issue_cd nn.issue_cd AND nn.severity_cd 'C'b
    ON a
    .cust_id b.cust_ida FULL outer join
    (SELECT DECODE(a.cust_id,NULLb.cust_ida.cust_idminor FROM (
      (
    SELECT  f.cust_id FROM furnace_issue fnetwork_issue nn
       WHERE f
    .org_id 'FT' AND f.org_id nn.org_id AND
             
    f.component_cd nn.component_cd AND f.issue_cd nn.issue_cd AND
       
    f.active_ind 'Y' AND nn.severity_cd 'W')) a
    FULL outer join
      
    (SELECT  f.cust_id FROM network_issue_log fnetwork_issue nn
       WHERE f
    .org_id 'FT' AND f.org_id nn.org_id AND f.component_cd nn.component_cd AND
             
    f.issue_cd nn.issue_cd AND nn.severity_cd 'W'b
    ON a
    .cust_id b.cust_idb
    ON a
    .critical b.minor
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    I think this works, but any comments or suggestions are welcome.

    at the top select ...
    PHP Code:
    SELECT 
      count
    (critical'critical'
      
    count(decode(criticalnullminornull)) 'minor' FROM ... 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    There's several ways you can rig it.

    Use some other keys instead of rowid
    carefully choose nvl substitution values


    Code:
    select
      count(critical),
      count((select minor 
    fromtemp_test
    wherenvl(t.critical,5)<>nvl(t.minor,4)
     androwid= t.rowid))
    fromtemp_test t
    
    Code:
    selectdistinct
      count(critical),
      x.minor_cnt
    from
      temp_test t,
      (selectcount(*) minor_cnt
       fromtemp_test 
       wherenvl(critical,5)<> minor
    	  androwid=rowid) x
    groupby x.minor_cnt
    

    Code:
    selectdistinct
      count(critical) over(),
      x.minor_cnt
    from
      temp_test t,
      (selectcount(*) minor_cnt
       fromtemp_test
       wherenvl(critical,5)<> minor
       androwid=rowid) x
    
    
    My way or the highway. Yeah

  4. #4
    Join Date
    May 2004
    Location
    Redwood Shores, CA
    Posts
    68
    Duck's is the slickest.
    My way or the highway. Yeah

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •