CODEWITHSUNDEEP
sqloracle-database
VamC
VamC

Reputation: 53

%Total from SQL Table

Using below code;

SELECT CASE GROUPING_ID(period) WHEN 1 THEN 'TOTAL' ELSE TO_CHAR(period) END AS period,
       COUNT(CASE status WHEN 'Completed'   THEN 1 END) AS completed,
       COUNT(CASE status WHEN 'WIP'         THEN 1 END) AS wip,
       COUNT(CASE status WHEN 'Not Started' THEN 1 END) AS not_started,
       COUNT(*) AS Total
  FROM transition_tasks_new
 GROUP BY ROLLUP(period)
 ORDER BY period;

I'm getting data in the attached format:

Screenshot attached

which is fine. However, I need to get the %complete for each of them. For ex - % Pre-Update "Complete" would be 2/28 i.e 7%;

I wrote this query;

SELECT SUM(CASE WHEN period = '1-Pre Update' THEN 1 ELSE 0 END) AS value,
       COUNT(taskid) AS max_value
  FROM transition_tasks_new
 WHERE status='Completed'

but it's not working as intended.

Upvotes: 0

Views: 54

Answers (2)

MT0
MT0

Reputation: 168106

Divide the value by the total and multiply by 100 to calculate the percentage:

SELECT CASE GROUPING_ID(period) WHEN 1 THEN 'TOTAL' ELSE TO_CHAR(period) END
         AS period,
       COUNT(CASE status WHEN 'Completed'   THEN 1 END) AS completed,
       ROUND(COUNT(CASE status WHEN 'Completed'   THEN 1 END)/COUNT(*) * 100, 3)
         AS percent_completed,
       COUNT(CASE status WHEN 'WIP'         THEN 1 END) AS wip,
       COUNT(CASE status WHEN 'Not Started' THEN 1 END) AS not_started,
       COUNT(*) AS total
FROM   tasks
GROUP BY ROLLUP(period)
ORDER BY period;

Which, for the sample data:

CREATE TABLE tasks (period, status) AS
SELECT 1, 'Completed'   FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 1, 'WIP'         FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 1, 'Not Started' FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 1, 'Other'       FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 2, 'Completed'   FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 2, 'WIP'         FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 3, 'Completed'   FROM DUAL CONNECT BY LEVEL <= 4 UNION ALL
SELECT 3, 'Not Started' FROM DUAL CONNECT BY LEVEL <= 2;

Outputs:

PERIOD COMPLETED PERCENT_COMPLETED WIP NOT_STARTED TOTAL
1 3 42.857 2 1 7
2 2 66.667 1 0 3
3 4 66.667 0 2 6
TOTAL 9 56.25 3 3 16

db<>fiddle here

Upvotes: 2

user15411679
user15411679

Reputation: 16

SELECT X.*,
    ROUND(X.COMPLETED / X.TOTAL * 100,
        2) AS PERCENT_COMPLETE
FROM
    (WITH SAMPLE_DATA AS
            (SELECT '1-Pre-Update' AS PERIOD,
                    3  AS WIP,
                    2  AS COMPLETED,
                    23 AS NOT_STARTED) SELECT PERIOD,
            SUM(WIP) AS WIP,
            SUM(COMPLETED) AS COMPLETED,
            SUM(NOT_STARTED) AS NOT_STARTED,
            SUM(WIP + COMPLETED + NOT_STARTED) AS TOTAL
        FROM SAMPLE_DATA
        GROUP BY PERIOD) X

Upvotes: 0

Related Questions