CODEWITHSUNDEEP
mysqlsql
Sanya
Sanya

Reputation: 73

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

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: 

A    101
A    103
B    101
B    102
C    102
D    101
E    101
E    102
E    103
E    105

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.

Upvotes: 1

Views: 105

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271031

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

Upvotes: 2

Related Questions