CODEWITHSUNDEEP
mysqlcountcasedistinct
Sallyerik
Sallyerik

Reputation: 519

How can I do a query with Count + Distinct+When?

I have this table (tableName):

|            IdPerson  | Date       | idPerson      |          idControls | Status
|                  125 | 2014-11-01 |           106 |                   4 | 1     |
|                  126 | 2014-12-01 |           109 |                   3 | 0     |
|                  127 | 2014-13-01 |           112 |                   2 | 1     |
|                  128 | 2014-14-01 |           115 |                   4 | 0     |
|                  129 | 2014-14-01 |           118 |                   3 | 0     |
|                  130 | 2014-16-01 |           121 |                   4 | 1     |

I got the right result executing these queries:

QUERY1:

select Date,count(distinct idControls) from tableName where Status=0 group by Date;
+------------+-------------------------------------+
| Date       | count(distinct idControls)          |
+------------+-------------------------------------+
| 2014-12-01 |                                   1 |
| 2014-14-02 |                                   2 |

QUERY2:

select Date,count(distinct idControls) from tableName where Status=1 group by Date;
+------------+-------------------------------------+
| Date       | count(distinct idControls)          |
+------------+-------------------------------------+
| 2014-11-01 |                                   1 |
| 2014-13-02 |                                   1 |
| 2014-16-02 |                                   1 |

But, What I wanted to do is to include both queries in just one.

Then, I'm trying to do something like this:

 select Date,sum(distinct(idControls) case when Status='1' then 1  else 0 end) fail ,sum(distinct case when Status='0' then 1 else 0 end) correct from tableName group by Date;

Obviously, the above Query doesn't work....

Any idea?

Upvotes: 1

Views: 38

Answers (1)

Sirko
Sirko

Reputation: 74076

A slightly different approach would be

SELECT `Date`,
       `status`,
       COUNT(DISTINCT `idControls`)
FROM `tableName` 
GROUP BY `Date`, `status`;

Easier query, but two result lines per date instead of one.

Upvotes: 2

Related Questions