CODEWITHSUNDEEP
mysqlsqlcount
fsi
fsi

Reputation: 1367

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

Upvotes: 0

Views: 151

Answers (5)

JanT
JanT

Reputation: 2096

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

Wouldn't this be all you need?

Upvotes: 0

user3741598
user3741598

Reputation: 295

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

Upvotes: 2

M4ver1k
M4ver1k

Reputation: 1575

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

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

Upvotes: 0

SQLChao
SQLChao

Reputation: 7837

You can group by type and value.

SQL Fiddle Example

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

Upvotes: 1

Barto
Barto

Reputation: 377

Is this what you need?

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

Upvotes: 2

Related Questions