0

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.

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149

2 Answers2

0

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

Something like this:

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

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

Nick Scotney
  • 269
  • 1
  • 2
  • 11
  • Thank you. This is giving me an SQL Syntax error near '*) AS... If I replace (*) with some column: FUNCTION root4.count does not exist. I am really new to this and any help is highly appreciated –  Dec 07 '22 at 12:19
  • 1
    @AzimFeta Sorry, that's an oversight from me - I've specified the table name outside the COUNT, when count is an aggregate function. See my updated answer, which should solve that issue – Nick Scotney Dec 07 '22 at 12:23
0

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

is there any way to print it in two columns as I post in the question? – Azim Feta

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
Akina
  • 39,301
  • 5
  • 14
  • 25