将MySQL中的两个计数分开

kiz8lqtg 于 2022-12-10 发布在 Mysql

关注(0)|答案(2)|浏览(137)

朋友们，我正在尝试从MySQL中分出两个COUNT(*)：我有这个问题：

SELECT 'Total ', COUNT(*) 
FROM root4 
WHERE str_to_date(DATE, '%d.%m.%Y') = CURDATE()  
UNION 
SELECT  'Good', COUNT(*) 
FROM root4 
WHERE str_to_date(DATE, '%d.%m.%Y') = CURDATE() 
AND testresult ='OK'

此查询的输出如下所示：

________________________
|Total | COUNT(*) |
________________________
|Total| 42       | 
|Good | 34       | 
_______________________

我想要实现的是在“Good”下创建另一行，称为“fpy”，但值是将“Good”除以“Total”的百分比。大概是这样的：

________________________
|Total | COUNT(*) |
________________________
|Total| 42       | 
|Good | 34       | 
|FPY  | 80.95    |
_______________________

我试着像新手一样把他们分开：

SELECT 'Total ', COUNT(*) 
FROM root4 
WHERE str_to_date(DATE, '%d.%m.%Y') = CURDATE()  
UNION 
SELECT  'Good', COUNT(*) 
FROM root4 WHERE str_to_date(DATE, '%d.%m.%Y') = CURDATE() 
AND testresult ='OK'                         
UNION 
SELECT 'FPY',  (COUNT(*) 
FROM root4 
WHERE str_to_date(DATE, '%d.%m.%Y') = CURDATE() / 
UNION 
SELECT  'Good', COUNT(*) 
FROM root4 
WHERE str_to_date(DATE, '%d.%m.%Y') = CURDATE() 
AND testresult ='OK')

当然，这是行不通的。
注意：列DATE是varchar，这就是我使用str_to_date的原因。

mysql

来源：https://stackoverflow.com/questions/74703986/dividing-two-counts-from-mysql

2条答案

按热度按时间

xzv2uavs1#

看看这个：

SELECT COUNT(*) AS Total,
       SUM(testresult ='OK') AS Good,
       100 * COUNT(*) / SUM(testresult ='OK') AS FPY
FROM root4 
WHERE `date` = DATE_FORMAT(CURRENT_DATE, '%d.%m.%Y')

有没有办法像我在问题中发布的那样，分成两栏打印？--阿齐姆·费塔

WITH cte AS (
    SELECT COUNT(*) AS Total,
           SUM(testresult ='OK') AS Good
    FROM root4 
    WHERE `date` = DATE_FORMAT(CURRENT_DATE, '%d.%m.%Y')
    )
SELECT 'Total' AS indicator, Total AS value FROM cte
UNION ALL
SELECT 'Good', Good FROM cte
UNION ALL
SELECT 'FPY', 100 * Good / Total FROM cte

赞(0）回复(0）举报 2022-12-10

gajydyqb2#

我想你可能需要一个“SubQuery”。
大概是这样的：

SELECT 
    Count(root4.*) AS Total,
    root4_1.Good AS Good,
    COUNT(root4.*) / root4_1.Good AS FYP
FROM 
    root4,
    (
        SELECT
            COUNT(*) AS Good
        FROM 
            root4 
        WHERE 
            str_to_date(DATE, '%d.%m.%Y') = CURDATE() 
        AND 
            testresult ='OK'  
    )AS root4_1 
WHERE 
    str_to_date(DATE, '%d.%m.%Y') = CURDATE()

另外，请参见这个问题，它类似于：How to SELECT based on value of another SELECT

赞(0）回复(0）举报 2022-12-10

我来回答

将MySQL中的两个计数分开

2条答案

相关问题

热门标签

最新问答