mysql - SUM 并从 MySQL 中的两个表中获取 AVG，并根据它们的 DATE 使用 WHERE 子句

Question

这是我的代码。我对此有困难。

SELECT NAMA_GRUP, SUM(TOTAL_KPI_PROGRAM)/COUNT(ctr.TANGGAL) AS AVG_KPI, COUNT(ctr.TANGGAL) AS JUMLAH_TANGGAL, ctr.TANGGAL AS TANGGAL
FROM ckm_t_grup ctg, ckm_t_program ctp, ckm_t_rundown ctr, ckm_t_calculate ctc
WHERE ctg.ID_GRUP = ctp.ID_GRUP AND ctp.ID_PROGRAM = ctr.ID_PROGRAM AND ctr.ID_RUNDOWN = ctc.ID_RUNDOWN AND (ctr.TANGGAL BETWEEN '2015-07-01' AND '2015-07-15')
GROUP BY ctr.TANGGAL, NAMA_GRUP

UNION

SELECT NAMA_GRUP, SUM(TOTAL_KPI_PROGRAM)/COUNT(ctr.TANGGAL) AS AVG_KPI, COUNT(ctr.TANGGAL) AS JUMLAH_TANGGAL, ctr.TANGGAL AS TANGGAL
FROM ckm_t_grup ctg, ckm_t_program ctp, ckm_t_rundown ctr, ckm_t_calculate_2 ctcc
WHERE ctg.ID_GRUP = ctp.ID_GRUP AND ctp.ID_PROGRAM = ctr.ID_PROGRAM AND ctr.ID_RUNDOWN = ctcc.ID_RUNDOWN AND (ctr.TANGGAL BETWEEN '2015-07-01' AND '2015-07-15')
GROUP BY ctr.TANGGAL, NAMA_GRUP

我只想对“AVG_KPI”列、JUMLAH_TANGGAL 求和，然后用 JUMLAH_TANGGAL 的总和计算“AVG_KPI”的 AVG。那么，这是我上面查询的结果。

这是我的查询结果图片:http://i.stack.imgur.com/VJ4ma.png

有人可以给我一些建议吗？我真的坚持了下来。

Answer 1

试试这个.....

SELECT NAMA_GRUP, SUM(TOTAL_KPI_PROGRAM)/COUNT(TANGGAL) AS AVG_KPI, COUNT(TANGGAL) AS JUMLAH_TANGGAL
(
    SELECT NAMA_GRUP, TOTAL_KPI_PROGRAM, ctr.TANGGAL
    FROM ckm_t_grup ctg, ckm_t_program ctp, ckm_t_rundown ctr, ckm_t_calculate ctc
    WHERE ctg.ID_GRUP = ctp.ID_GRUP AND ctp.ID_PROGRAM = ctr.ID_PROGRAM AND ctr.ID_RUNDOWN = ctc.ID_RUNDOWN AND (ctr.TANGGAL BETWEEN '2015-07-01' AND '2015-07-15')
    UNION
    SELECT NAMA_GRUP, TOTAL_KPI_PROGRAM, ctr.TANGGAL
    FROM ckm_t_grup ctg, ckm_t_program ctp, ckm_t_rundown ctr, ckm_t_calculate_2 ctcc
    WHERE ctg.ID_GRUP = ctp.ID_GRUP AND ctp.ID_PROGRAM = ctr.ID_PROGRAM AND ctr.ID_RUNDOWN = ctcc.ID_RUNDOWN AND (ctr.TANGGAL BETWEEN '2015-07-01' AND '2015-07-15')
) as tmp
GROUP BY NAMA_GRUP, TANGGAL

这将解决您的问题