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!

Hi edx. What have you tried so far? regards, Fazza

So far i still cant figure out how to make it done, do u have any idea?

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.

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?