fsi fsi - 1 month ago 18
MySQL Question

Mysql logic to count

I have my table below:

| id | version | type | test_id | value |
+------+---------+----------+---------+--------------------+
| 1235 | 0 | 600 | 111 | 2 |
| 1236 | 0 | 600 | 111 | 2 |
| 1237 | 0 | 600 | 111 | 2 |
| 1238 | 0 | 601 | 111 | 1 |
| 1239 | 0 | 602 | 111 | 1 |
| 1240 | 0 | 600 | 111 | 1 |
| 1241 | 0 | 601 | 111 | 1 |


I'm trying to retrieve the count dependents of the column value. Type 600 has three values of 2 and one value of 1. So I need the result 3 and 1. My co-worker told me to use distinct but I think I'm using wrong syntax?

(select distinct a.type from Answer a where type = 600)
union
(select distinct a.value from Answer a where type = 600)
union
(select count(value) from Answer where type = 600 and value = 2);

Answer

You can group by type and value.

SQL Fiddle Example

 select type,
   value,
   count(*)
 from answer
 -- add your where clause
 group by type, value