Is there a way I can get output of count values in MySql?

Question

I am dealing with getting the output of some common values. My database is quite big so I am just giving a dummy database here for understanding purposes.

Here is the table I have:

I have to extract only those variables that have the given common room numbers alotted. This is the table for the required data:

101
103

Its output should be like this:

A
E

A and E, both variables have common room values i.e. 101 and 103

I came up with this query:

I have to print this, ones with count as 2!!

select count(*)
from table1, table2
where table1.room_no = table2.rooms
group by table1.variables
;

Any help would be appreciated.

@Strawberry hi, yes I know. I just gave a raw data to explain the situation I am currently in. Just giving a brief. Thanks — Sanya
– Sanya, Commented Feb 23, 2020 at 21:51

Gordon Linoff · Accepted Answer · 2020-02-22 23:17:37Z

2

I have to extract only those variables that have the given common room numbers allotted.

I don't know what your table or columns are called, but you can use aggregation and having:

select col1
from t
where col2 in (1, 3)   -- list of common items
group by col1
having count(*) = 2;   -- size of list

If duplicates are allowed in the data, then use count(distinct col2) in the having clause.

If these values are stored in a second table, then just use:

select t1.col1
from table1 t1 join
     table2 t2
     on t1.value = t2.value
group by t1.col1
having count(*) = (select count(*) from table2);

edited Feb 22, 2020 at 23:17

answered Feb 22, 2020 at 23:02

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Is there a way I can get output of count values in MySql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related