Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Exclamation Unanswered: Select statement within the select statment

    Below is a statement that give me the results as follows:
    --------------------
    Tester | Total_Target_Cycle
    tester1 | 5
    tester2 | 6

    I am trying to figure out how i can get total_defects column with only open defects only and percent change between the target cycle and total defects.
    number 10 and 8 should be under total defects.
    ------------------------------------------------------
    Tester | Total_Target_Cycle | Total_Defects| % Change
    ------------------------------------------------------
    tester1 | 5 10
    tester2 | 6 8



    BELOW ARE TWO STATMENT I AM TRYING TO COMBINE.

    statement one
    select BG_detected_by Tester,COUNT(*)Total_Target_Cycle
    From Bug
    where BG_STATUS = 'Open'and BG_TARGET_RCYC IS NOT NULL
    GROUP by BG_DETECTED_BY, BG_STATUS,BG_TARGET_RCYC

    statement two
    select bg_detected_by tester, count(*) Total
    from bug
    where bg_status = 'Open'
    group by bg_status,bg_detected_by

    THIS IS THE STATEMENT i HAVE SO FAR. BUT IS GIVING ME "SINGLE-ROW SUBQUERY ERROR MESSAGE.

    select BG_detected_by Tester,
    COUNT(BG_Target_RCYC) Defects,
    (COUNT(BG_Target_RCYC) / (select count(*) from bug b2 where bg_status = 'Open' and b1.bg_target_rcyc = b2.bg_target_rcyc group by bg_status,bg_detected_by)) * 100 as percentage
    From Bug b1
    where BG_STATUS = 'Open' AND BG_TARGET_RCYC IS NOT NULL
    GROUP by BG_DETECTED_BY, BG_STATUS,BG_TARGET_RCYC

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    The easiest way of combining the two queries would be differing the counts by adding the extra condition into COUNT function, something like:
    Code:
    select BG_detected_by Tester,
      COUNT( case when BG_TARGET_RCYC IS NOT NULL then 1 end ) Total_Target_Cycle,
      count(*) Total
    From Bug
    where BG_STATUS = 'Open'
    GROUP by BG_DETECTED_BY, BG_STATUS,BG_TARGET_RCYC
    There is no need to GROUP BY BG_STATUS column (as it can only be 'Open').
    Grouping by BG_TARGET_RCYC might lead to result with multiple rows with the same BG_DETECTED_BY value - the result would look ambiguously to me. Its use depends on actual requirements; the query would not fail after its removal though.

  3. #3
    Join Date
    Sep 2010
    Posts
    3

    Question Select within Select

    Quote Originally Posted by flyboy View Post
    The easiest way of combining the two queries would be differing the counts by adding the extra condition into COUNT function, something like:
    Code:
    select BG_detected_by Tester,
      COUNT( case when BG_TARGET_RCYC IS NOT NULL then 1 end ) Total_Target_Cycle,
      count(*) Total
    From Bug
    where BG_STATUS = 'Open'
    GROUP by BG_DETECTED_BY, BG_STATUS,BG_TARGET_RCYC
    There is no need to GROUP BY BG_STATUS column (as it can only be 'Open').
    Grouping by BG_TARGET_RCYC might lead to result with multiple rows with the same BG_DETECTED_BY value - the result would look ambiguously to me. Its use depends on actual requirements; the query would not fail after its removal though.
    I have updated the sql statement and deleted the bg_target_rcyc is not null
    I can get the following results table, but I need to know how i can put a Percentage column with a difference between Total_Target_Cycle divide by Defect

    Tester Total_Target_Cycle Defect


    select bg_detected_by tester,count(bg_target_rcyc)Total_Target_Cycle,cou nt(bg_status) defect
    from bug b1
    where bg_status = 'Open'
    group by bg_status,bg_detected_by

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by pitbull1 View Post
    I have updated the sql statement and deleted the bg_target_rcyc is not null
    Yes, that change has the same functionality and it is simpler.
    Quote Originally Posted by pitbull1 View Post
    I can get the following results table, but I need to know how i can put a Percentage column with a difference between Total_Target_Cycle divide by Defect
    Ehm, what about dividing these two figures using '/' operator? Or are there aggregates from another rows? Of course, posting some sample data with expected result (preferably as described in this link: http://tkyte.blogspot.com/2005/06/ho...questions.html) would help to understand it.

    And, lastly, formatting the code using CODE tags would be great too (just compare yours and my posts). It is described in the sticky (the very first post in this forum - http://www.dbforums.com/oracle/10316...s-posters.html). Or, just use '#' button on selected code text.

  5. #5
    Join Date
    Sep 2010
    Posts
    3

    Question

    Quote Originally Posted by flyboy View Post
    Yes, that change has the same functionality and it is simpler.

    Ehm, what about dividing these two figures using '/' operator? Or are there aggregates from another rows? Of course, posting some sample data with expected result (preferably as described in this link: The Tom Kyte Blog: How to ask questions) would help to understand it.

    And, lastly, formatting the code using CODE tags would be great too (just compare yours and my posts). It is described in the sticky (the very first post in this forum - http://www.dbforums.com/oracle/10316...s-posters.html). Or, just use '#' button on selected code text.

    Thanks for the useful info. Below is what I am looking for in the final results. I did the following but no luck instead is show up with duplicate users with multiple target_rcyc which is correct but rather have them combine under one user see sample below.

    select bg_detected_by tester,count(bg_target_rcyc)Total_Target_Cycle,cou nt(bg_status) total_defect,(count(bg_target_rcyc)/(select count(bg_status) from bug b2
    where b2.bg_target_rcyc = b1.bg_target_rcyc)) * 100 Percentage
    from BUG b1
    where bg_target_rcyc IS NOT NULL AND BG_STATUS = 'Open'
    group by bg_status,bg_detected_by,bg_target_rcyc

    --------------------------------------------------------------------------------
    Tester |Sum of Total Target Cycle | Sum of TOTAL_DEFECT | Percentage
    -------------------------------------------------------------------------------
    Tester----------0 -------------------------- 1----------- 100%
    ff-------------- 1------------------------------- 2 50.00%
    dfdf------------ 3------------------------------- 6 50.00%
    dsfdfs---------- 35------------------------------- 42 83.33%
    fdfsdf ---------- 9 14 64.29%
    dfsdf 1 1 100.00%
    mbrunner 1 3 33.33%
    sdfdfs 4 7 57.14%
    afd 0 3 0.00%
    swang 1 3 33.33%
    dffds 0 1 0.00%
    fdfd 1 1 100.00%
    Grand Total 56 84 66.67%
    Last edited by pitbull1; 09-16-10 at 12:21.

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by pitbull1 View Post
    Thanks for the useful info. Below is what I am looking for in the final results. I did the following but no luck.
    "No luck" is unfortunately no Oracle message, so it is impossible to tell what is wrong.
    Is the result you posted generated from that query or is it the desired one? In the second case, the very first row seems wrong ( 0/1 != 1 ).
    I wonder why are you insisting on that subquery in SELECT clause and do not simply calculate
    Code:
    count(bg_target_rcyc) / count(bg_status)
    And, of course,
    formatting the code using CODE tags would be great too (just compare yours and my posts). It is described in the sticky (the very first post in this forum - http://www.dbforums.com/oracle/10316...s-posters.html). Or, just use '#' button on selected code text.
    It really hurts (not only) my eyes to read your posts.

Posting Permissions

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