%Total from SQL Table-CodePudding

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.

CodePudding user response：

There is something wrong in your basic query/data itself. 3 1 !=5

Can you please,

Provide DDL statement (change all private/confidential information)
Provide Insert statements of sample data
Expected output in a visual format.

CodePudding user response：

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