Dividing two COUNTS from mysql-CodePudding

Friends, I am trying to divide two COUNT(*) from MySQL: I have this query:

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'

The output of this query is looking like this:

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

What I want to achieve is to make another row under "Good" called "FPY" but the value to the dividing of "Good" to "Total" in percentage. Something like this:

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

I tried to divide them like noob:

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')

Of course, this is not working...

Note: Colum DATE is varchar that`s why I am using str_to_date.

CodePudding user response：

I think you could be needing a "SubQuery" here.

Something like this:

SELECT 
    root4.Count(*) AS Total,
    root4_1.Good AS Good,
    root4.COUNT(*) / 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()

Also, see this question, which is similar: How to SELECT based on value of another SELECT

CodePudding user response：

Look for this:

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')