CODEWITHSUNDEEP
phpmysql
ODelibalta
ODelibalta

Reputation: 2254

MySQL Merge Queries on same table

I am trying to do is this

I have id numbers and criteria column with either 0 or 1 value. I want to get the percentages for those. 

SELECT COUNT(id) FROM table; /* ALL COUNT */
SELECT COUNT(id) FROM table WHERE criteria='1' /* pass count */ 
SELECT COUNT(id) FROM table WHERE criteira='0' /* fail count */

Thats the very basic of it but not what I am trying to do. I need the pass and fail percentages. How do I get that ?

Upvotes: 1

Views: 48

Answers (3)

Santhosh
Santhosh

Reputation: 1791

Here is the SQL fiddle

SELECT
ROUND((SUM(CASE WHEN CRITERIA = '1' THEN 1 ELSE 0 END)/SUM(1)) * 100, 2) AS PASS_PCT,
ROUND((SUM(CASE WHEN CRITERIA = '0' THEN 1 ELSE 0 END)/SUM(1)) * 100, 2) AS FAIL_PCT
FROM SAMPLE

Upvotes: 0

Mehran
Mehran

Reputation: 16911

Almost the same as the other post, but I think the use of IF is inescapable!

SELECT
    COUNT(*) AS `count`
    , SUM(IF(criteria='1', 1, 0)) AS `pass`
    , SUM(IF(criteria='0', 1, 0)) AS `fail`
FROM `table`

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Try this using COUNT() and SUM() with condition also using aggregate functions without grouping then will result in one row ,sum with condition will return as boolean so if conditon matches it results as true and will work as count

SELECT COUNT(id) `all`
,SUM(criteria='1') `pass`
,SUM(criteria='0') `fail`
FROM `table`

Upvotes: 3

Related Questions