sub query returning more than one row

Question

I am trying a query like this in MYSQL

select 
Sum(case when WindowsXP = "PASS" then 1 else 0 end) as PASS ,
Sum(case when WindowsVista = "FAIL" then 1 else 0 end) as FAIL 

from OS_Table where BuildID = (select distinct BuildID from OS_Table)
group by BuildID

The error is Subquery returns more than one row. If I use IN instead of = then the query is going on forever ( nearly after 3 minutes it does not stop)

Basically what I am trying to achieve is for each distinct BuildID, give me counts of PASS, FAIL when WindowsXP = "PASS" and WindowsVista = "FAIL"

I have hardly 10 distinct BuildID's

How do I achieve this?

Answer 1

Just remove your join, it's redundant:

SELECT  buildid,
        SUM(CASE WHEN WindowsXP = 'PASS' THEN 1 ELSE 0 END) as PASS ,
        SUM(CASE WHEN WindowsVista = 'FAIL' THEN 1 ELSE 0 END) as FAIL 
FROM    OS_Table
GROUP BY
        buildId

This condition

WHERE BuildID IN (SELECT DISTINCT BuildID FROM OS_Table)

holds for any non-null buildid in the table.

Update:

Your original query (with = operator) meant this: "take all records from the table where buildId equals to a single DISTINCT value of buildId taken from the same table, split them into several groups according to the value of buildID and calculate the sums of the expressions within each group".

= operator requires a scalar on both sides. In SQL, a query is considered a scalar if and only if it returns a recordset of one field and at most one record.

Your subquery returned more that one record, so you original query failed (with quite a descriptive error).

With IN operator, the query meant "take all records from the table where buildId is found anywhere in the list of buildId's taken from the same table, split them into several groups according to the value of buildID and calculate the sums of the expressions within each group".

Since buildId is always found the in the list of buildIs taken from the same table, the condition is redundant.

Answer 2

If you want your output to include all BuildIDs, you don't need a WHERE at all:

SELECT
    BuildID
    , SUM(CASE WHEN WindowsXP = "PASS" THEN 1 ELSE 0 END) AS PASS
    , SUM(CASE WHEN WindowsVista = "FAIL" THEN 1 ELSE 0 END) AS FAIL
FROM
    OS_Table
GROUP BY
    BuildID

disclaimer: untested.