# Thread: counting DONE and NOT DONE totals (was "Newbie SQL problem")

1. Registered User
Join Date
Aug 2005
Posts
11

## 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%

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

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

4. Registered User
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. Registered User
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

-----------------------------------------------------------------------
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,