mysql - 同一查询中的计数和百分比

Question

我使用以下查询来获取基于 WHERE 参数的计数。

我希望能够包含基于计数(i131ID)的每个组的百分比，但我似乎无法让查询返回结果。

原始查询:

SELECT MAX(counthypo) counthypo, MAX(counteuthyroid) counteuthyroid, MAX(counthyper) counthyper, MAX(none) none, MAX(unknown) unknown

FROM (
      SELECT count(*) as counthypo, 0 as counteuthyroid, 0 as counthyper, 0 as none, 0 as unknown
      FROM tbl_I131_data
      WHERE `recheck_t4` < `recheck_t4_range_low` 

      UNION 

      SELECT 0 as counthypo, count(*) as counteuthyroid, 0 as counthyper, 0 as none, 0 as unknown
      FROM tbl_I131_data
      WHERE (`recheck_t4` BETWEEN `recheck_t4_range_low` AND `recheck_t4_range_high`)

      UNION

      SELECT 0 as counthypo, 0 as counteuthyroid, count(*) as counthyper, 0 as none, 0 as unknown
      FROM tbl_I131_data
      WHERE `recheck_t4` > `recheck_t4_range_high` 

      UNION

      SELECT 0 as counthypo, 0 as counteuthyroid, 0 as counthyper, count(*) as none, 0 as unknown
      FROM tbl_I131_data
      WHERE `nordvmfollowup` = '1' AND `isotope` = '1'

      UNION

      SELECT 0 as counthypo, 0 as counteuthyroid, 0 as counthyper, 0 as none, count(*) as unknown
      FROM tbl_I131_data      
      WHERE `recheck_t4` is null AND `isotope` = '1' and `nordvmfollowup` is null

     ) i

我尝试过使用 CROSS JOIN

CROSS JOIN
(SELECT COUNT(*) as total
FROM tbl_I131_data i131
) i2

然后在最初的 SELECT 中，我尝试了类似的操作

SELECT MAX(counthypo) counthypo, (counthypo/total)*100 AS percHypo, MAX(counteuthyroid) counteuthyroid, MAX(counthyper) counthyper, MAX(none) none, MAX(unknown) unknown

我就是无法理解它。

Answer 1

我认为你只需要按总数分组所以整个查询将是

SELECT MAX(counthypo) AS counthypo, (MAX(counthypo)/total)*100 AS percHypo, MAX(counteuthyroid) counteuthyroid, MAX(counthyper) counthyper, MAX(none) none, MAX(unknown) unknown

FROM (
       SELECT count(*) as counthypo, 0 as counteuthyroid, 0 as counthyper, 0 as none, 0 as unknown
       FROM tbl_I131_data

       WHERE `recheck_t4` < `recheck_t4_range_low`

       UNION

       SELECT 0 as counthypo, count(*) as counteuthyroid, 0 as counthyper, 0 as none, 0 as unknown
       FROM tbl_I131_data
       WHERE (`recheck_t4` BETWEEN `recheck_t4_range_low` AND `recheck_t4_range_high`)

       UNION

       SELECT 0 as counthypo, 0 as counteuthyroid, count(*) as counthyper, 0 as none, 0 as unknown
       FROM tbl_I131_data
       WHERE `recheck_t4` > `recheck_t4_range_high`

       UNION

       SELECT 0 as counthypo, 0 as counteuthyroid, 0 as counthyper, count(*) as none, 0 as unknown
       FROM tbl_I131_data
       WHERE `nordvmfollowup` = '1' AND `isotope` = '1'

       UNION

       SELECT 0 as counthypo, 0 as counteuthyroid, 0 as counthyper, 0 as none, count(*) as unknown
       FROM tbl_I131_data
       WHERE `recheck_t4` is null AND `isotope` = '1' and `nordvmfollowup` is null

     ) i
CROSS JOIN
(SELECT COUNT(*) as total
 FROM tbl_I131_data as i131
) i2
group by i2.total