Mysql logic to count

Question

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);

score 2 · Answer 1 · edited Jul 29 '14 at 19:37

2

select value, count(*) from a where type=600 group by value

edited Jul 29 '14 at 19:37

JasonMArcher

14,195
22
56
52

answered Jul 29 '14 at 19:00

user3741598

297
1
12

score 2 · Answer 2 · answered Jul 29 '14 at 19:01

2

Is this what you need?

SELECT type, value, COUNT(*)
FROM Answer
GROUP BY 1, 2;

answered Jul 29 '14 at 19:01

Barto

337
2
11

1

This answer could be more generic by not hard coding for value 1 or 2. – JasonMArcher Jul 29 '14 at 19:36

score 1 · Accepted Answer · answered Jul 29 '14 at 19:04

1

You can group by type and value.

SQL Fiddle Example

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

answered Jul 29 '14 at 19:04

SQLChao

7,709
1
17
32

JanT · Answer 4 · 2016-10-20T15:45:15.130

0

select type, count(value) 
from table
where type = 600
group by type

Wouldn't this be all you need?

edited Oct 20 '16 at 15:45

answered Jul 29 '14 at 18:59

JanT

2,105
3
28
35

score 0 · Answer 5 · edited May 23 '17 at 10:27

0

SELECT TYPE,VALUE,COUNT(*) FROM Answer 
GROUP BY type,value

You can group based on multiple columns please refer this question for more details.

edited May 23 '17 at 10:27

Community

1
1

answered Jul 29 '14 at 19:05

M4ver1k

1,505
1
15
26

From each line, it gives me 1. Which the case I'm trying is get 3 and 1 value. – fsi Jul 29 '14 at 19:08
please include other necessary fields as well , i have edited the answer. – M4ver1k Jul 29 '14 at 19:13

Mysql logic to count

5 Answers5