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

Barto · Accepted Answer · 2014-07-29 19:01:51Z

2

Is this what you need?

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

answered Jul 29, 2014 at 19:01

Barto

3773 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JasonMArcher Over a year ago

This answer could be more generic by not hard coding for value 1 or 2.

JasonMArcher · Accepted Answer · 2014-07-29 19:37:19Z

2

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

edited Jul 29, 2014 at 19:37

JasonMArcher

15.1k22 gold badges59 silver badges53 bronze badges

answered Jul 29, 2014 at 19:00

user3741598

2951 silver badge12 bronze badges

Comments

SQLChao · Accepted Answer · 2014-07-29 19:04:42Z

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, 2014 at 19:04

SQLChao

7,8851 gold badge19 silver badges33 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:27:31Z

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, 2017 at 10:27

CommunityBot

11 silver badge

answered Jul 29, 2014 at 19:05

M4ver1k

1,5951 gold badge15 silver badges26 bronze badges

2 Comments

fsi Over a year ago

From each line, it gives me 1. Which the case I'm trying is get 3 and 1 value.

M4ver1k Over a year ago

please include other necessary fields as well , i have edited the answer.

JanT · Accepted Answer · 2016-10-20 15:45:15Z

0

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

Wouldn't this be all you need?

edited Oct 20, 2016 at 15:45

answered Jul 29, 2014 at 18:59

JanT

2,0963 gold badges30 silver badges38 bronze badges

Collectives™ on Stack Overflow

Mysql logic to count

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related