Question 21878 in Mysql Question posted in
The official MySQL documentation can be found here.

Dividing two COUNTS from mysql

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.

Tags:

2

Answers


  1. 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

    Login or Signup to reply.
  2. 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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search