Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    11

    Red face Unanswered: counting DONE and NOT DONE totals (was "Newbie SQL problem")

    Hello all,

    i'm a newbie in sql, and currently i am facing some problems in sql. My questions is as shown:

    I have a table with about 30,000 records, the sample columns of this table is as following:

    ------------------------------------------------------------------
    CPN | MPN | Status | Requester | Project_Name | ReceivedDate
    ------------------------------------------------------------------
    CN8 CNA1 DONE John SUN 2006/03/01
    CN8 CNA2 NOT_DONE John SUN 2006/03/01
    BF7 GHE1 DONE Alex MICRO 2006/04/01
    BF8 GHE2 DONE Alex MICRO 2006/04/01
    BF9 GHE3 NOT_DONE Alex MICRO 2006/04/01
    BF0 GHE4 NOT_DONE Alex MICRO 2006/04/01


    How can i make it to become:
    -------------------------------------------------------------------
    Project_Name | Requester | ReceivedDate | Total_DONE(%) |Total_CPN(%)| DONE | NOT_DONE | Total |

    SUN | John | 2006/03/01 | 50% | 100% | 1 | 1 | 2
    MICRO| Alex | 2006/04/01 | 50% | 50% | 2 | 2 | 4


    Total_DONE(%) is base on how many % of MPN done
    Total_CPN(%) is base on how many % of CPN done


    For example, in John case, 'CNA1' is done while 'CNA2' is not done, so Total_DONE(%) = 50%, but since both MPN is under same CPN (CN8), so once one of the MPN under this CPN is done, it considered done for Total_CPN(%), so Total_CPN(%) = 100%

    Any unclear and doubt please ask me in the thread.
    Your advices and help is much appreciated, thanks!

  2. #2
    Join Date
    Feb 2006
    Posts
    113
    Hi edx. What have you tried so far? regards, Fazza

  3. #3
    Join Date
    Aug 2005
    Posts
    11
    So far i still cant figure out how to make it done, do u have any idea?

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Here's a hint. Try using a CASE statement to convert your DONE and NOT_DONE to zeros and ones. Then SUM and divide the results to get your percentages.

  5. #5
    Join Date
    Aug 2005
    Posts
    11
    Thanks for the reply urquel, ya, it's work by using the 'case' method for Total_DONE(%), but how bout the Total_CPN(%)?Since the Total_CPN have nested group method involved, whereby after group by 'project_name', i need to group again by the CPN, means if one of the MPN under same CPN is DONE, it's considered DONE..

    Here is my progress so far

    -----------------------------------------------------------------------
    SELECT project_name , requester, ReceiveDate,
    COUNT(CASE WHEN Status = 'DONE' THEN 1 END) * 100 / COUNT(*) as [Total_DONE(%)],
    COUNT(CASE WHEN Status = 'DONE' THEN 1 END) as DONE,
    COUNT(CASE WHEN Status = 'NOT_DONE' THEN 1 END) as X_DONE,
    COUNT(*) as TOTAL,
    GROUP BY Project_Name, Requester, ReceivedDate

    -----------------------------------------------------------------------

    Still having trouble for the CPN(%)...anyone able to help?

Posting Permissions

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