patternsqlMinor
Mysql match on two columns and get count
Viewed 0 times
columnsmatchmysqltwogetandcount
Problem
How do I get a count of?
In MYSQL how do I get a count on Names that match only if Month also match?
IE
Joe April should be only one...
+---------+-------------+
| col1 | col2 |
+---------+-------------+
| Joe | April |
| Fred | May |
| Sam | April |
| Joe | April |
| Mark | Jan |
| Joe | Dec |
+---------+-------------+In MYSQL how do I get a count on Names that match only if Month also match?
IE
Joe April should be only one...
Solution
@Abdul's answer just needs
The query shold look like this now
Here is the execution of it:
If you do not care for the count, remove it from the SELECT clause
Give it a Try !!!
HAVING COUNT(*) > 1The query shold look like this now
SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;Here is the execution of it:
mysql> create table test(col1 varchar(20),col2 varchar(20));
Query OK, 0 rows affected (0.33 sec)
mysql> INSERT INTO test VALUES('Joe','April'),('Fred','May'),
-> ('Sam','April'),('Joe','April'),('Mark','Jan'),('Joe','Dec');
Query OK, 6 rows affected (0.06 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+----------+------+-------+
| COUNT(*) | col1 | col2 |
+----------+------+-------+
| 2 | Joe | April |
+----------+------+-------+
1 row in set (0.24 sec)
mysql>If you do not care for the count, remove it from the SELECT clause
mysql> SELECT col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+------+-------+
| col1 | col2 |
+------+-------+
| Joe | April |
+------+-------+
1 row in set (0.00 sec)
mysql>Give it a Try !!!
Code Snippets
SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;mysql> create table test(col1 varchar(20),col2 varchar(20));
Query OK, 0 rows affected (0.33 sec)
mysql> INSERT INTO test VALUES('Joe','April'),('Fred','May'),
-> ('Sam','April'),('Joe','April'),('Mark','Jan'),('Joe','Dec');
Query OK, 6 rows affected (0.06 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+----------+------+-------+
| COUNT(*) | col1 | col2 |
+----------+------+-------+
| 2 | Joe | April |
+----------+------+-------+
1 row in set (0.24 sec)
mysql>mysql> SELECT col1 , col2 FROM test GROUP BY col1,col2 HAVING COUNT(*) > 1;
+------+-------+
| col1 | col2 |
+------+-------+
| Joe | April |
+------+-------+
1 row in set (0.00 sec)
mysql>Context
StackExchange Database Administrators Q#55603, answer score: 4
Revisions (0)
No revisions yet.