我一TABLE，FIELD有單位，課程，狀態（計划，完成） 希望的結果是，跑出各單位的完成的課程占總課程的百分比

Hi james_yu,    Sorry !! 不知你是用何資料庫，下列語法你試試，不知是否可行？

SELECT  單位,
        (SUM(FINISHED) / SUM(PLANED)) * 100 AS 完成百分比
FROM   (SELECT  單位,
                COUNT(*) AS PLANED,
                0 AS FINISHED
        FROM    YOURTABLE
        GROUP BY
                單位
        UNION ALL
        SELECT  單位,
                0 AS PLANED,
                COUNT(*) AS FINISHED
        FROM    YOURTABLE
        WHERE   狀態 = '完成'
        GROUP BY
                單位)
GROUP BY
        單位

發表人 -

------
Fishman

我的意思是跑出各單位的完成的課程占各部門總課程的百分比    我一TABLE，FIELD有單位，課程，狀態（計划，完成） 希望的結果是，跑出各單位的完成的課程占這個單位總課程(計劃+完成)的百分比     另外,我的數據庫為oracle8

Hi james_yu,    沒有錯啊！ 資料如下： 執行結果如下：

SQL Command:
SELECT  DEPARTMENT,
        (SUM(FINISHED) / SUM(PLANED)) * 100 AS FINISH_PERCENT
FROM   (SELECT  DEPARTMENT,
                COUNT(*) AS PLANED,
                0 AS FINISHED
        FROM    TABLE1
        GROUP BY
                DEPARTMENT
        UNION ALL
        SELECT  DEPARTMENT,
                0 AS PLANED,
                COUNT(*) AS FINISHED
        FROM    TABLE1
        WHERE   STATUS = 'FINISHED'
        GROUP BY
                DEPARTMENT)
GROUP BY
        DEPARTMENT

發表人 -

------
Fishman

select units,(sum(finished)/sum(planed))*100 as 完成百分比 from ((select units,count(*) as planed,0 as finished from train_schedule group by units) union all (select units,0 as planed,count(*) as finished from train_schedule where state='完成' group by units )) group by units / 已經OK,感謝FISHMAN,簡直太愛你了,即使我也是個男人