Group the rows that are having the same value in specific field in MySQL

Question

I am having the following data in MySQL table.

+--------+----------+------+
| job_id | query_id | done |
+--------+----------+------+
|  15145 | a002     |    1 |
|  15146 | a002     |    1 |
|  15148 | a002     |    1 |
|  15150 | a002     |    1 |
|  15314 | a003     |    0 |
|  15315 | a003     |    1 |
|  15316 | a003     |    0 |
|  15317 | a003     |    0 |
|  15318 | a003     |    1 |
|  15319 | a003     |    0 |
+--------+----------+------+

I would like to know if it's possible to have a sql query, which can group by query_id IF ALL 'done' fields are marked as 1. The possible output I imagine would be:

+----------+------+
| query_id | done |
+----------+------+
|  a002    |  1   |
|  a003    |  0   |
+----------+------+

I've tried the following SQL query:

select job_id, query_id, done from job_table group by done having done = 1 ;

But no luck. I would really appreciate for any help!

Answer 1

I'm not particullarly proud of this solution because it is not very clear, but at least it's fast and simple. If all of the items have "done" = 1 then the sum will be equal to the count SUM = COUNT

SELECT query_id, SUM(done) AS doneSum, COUNT(done) AS doneCnt 
FROM tbl 
GROUP BY query_id

And if you add a having clause you get the items that are "done".

HAVING doneSum = doneCnt

I'll let you format the solution properly, you can do a DIFERENCE to get the "not done" items or doneSum <> doneCnt.

Btw, SQL fiddle here.

Answer 2

All "done" jobs :

SELECT * FROM job_table GROUP BY done HAVING MIN(done) = 1;

All "undone" jobs :

SELECT * FROM job_table GROUP BY done HAVING MAX(done) = 0;

All jobs having the same value ("done" OR "undone") :

SELECT * FROM job_table GROUP BY done HAVING MAX(done) = 0 OR MIN(done) = 1;