CODEWITHSUNDEEP
mysqlsqlselectcount
user1946705
user1946705

Reputation: 2888

MySQL - how to get count of rows

I have these data in database:

+----+------------+---------+
| id | col1       | col2    |
+----+------------+---------+
|  1 |          3 |       2 |
|  2 |          2 |       3 |
|  3 |          4 |       5 |
+----+------------+---------+

I am trying to do a query, that will give me a count of rows, in which are the same numbers. I know the value one of them (the numbers).

In this case, the same numbers are 2 and 3 (in the columns col1 and col2) - I am trying to get from database number 2 (two rows). I have available always one of two numbers - in this case number 3.

Is possible to do this query? Thanks for help.

Upvotes: 1

Views: 253

Answers (3)

True Soft
True Soft

Reputation: 8796

With this query:

select count(*) count_rows
from table1 
where col1=3 or col2=3
group by concat(IF(col1<col2, col1, col2), ',', IF(col1<col2, col2, col1))

you get the result 2.

Replace the 3 from the query in the where clause if there is another input.

The value for concat(IF(col1<col2, col1, col2), ',', IF(col1<col2, col2, col1)) would be "2,3" for records 1-2, (and "4,5" for the third record).

Upvotes: 0

Adriano Carneiro
Adriano Carneiro

Reputation: 58665

For the very narrow scenario you specified, I would try this:

select count(*)
from myTable mt1 
     inner join myTable mt2 on (mt1.col1 = mt2.col2 AND mt2.col1 = mt1.col2)
and mt1.col1 = 3 --here goes the known value

This only works for the data you posted. Please let us know the count for the following scenario:

+----+------------+---------+
| id | col1       | col2    |
+----+------------+---------+
|  1 |          3 |       2 |
|  2 |          2 |       3 |
|  3 |          3 |       2 |
|  4 |          3 |       5 |
|  5 |          4 |       5 |
+----+------------+---------+

Upvotes: 2

a1ex07
a1ex07

Reputation: 37382

Are you looking something like

SELECT (col1+col2) , COUNT(*) AS num_rows
FROM table1
WHERE col1 =3 OR col2 =3
GROUP BY (col1+col2)
HAVING num_rows >1

Upvotes: 0

Related Questions