3

How do I get a count of?

+---------+-------------+
|   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...

1
  • I hope count of Joe April should be 2. Dec 26, 2013 at 5:29

2 Answers 2

4

@Abdul's answer just needs HAVING COUNT(*) > 1

The 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 !!!

0

Create a table and insert records

create table test(col1 varchar(20),col2 varchar(20));

INSERT INTO test VALUES('Joe','April'),('Fred','May'),('Sam','April'),('Joe','April'),('Mark','Jan'),('Joe','Dec');

SELECT * FROM test;
+------+-------+
| col1 | col2  |
+------+-------+
| Joe  | April |
| Fred | May   |
| Sam  | April |
| Joe  | April |
| Mark | Jan   |
| Joe  | Dec   |
+------+-------+
6 rows in set (0.00 sec)

Find actual result

SELECT COUNT(*), col1 , col2 FROM test GROUP BY col1,col2;
+----------+------+-------+
| COUNT(*) | col1 | col2  |
+----------+------+-------+
|        1 | Fred | May   |
|        2 | Joe  | April |
|        1 | Joe  | Dec   |
|        1 | Mark | Jan   |
|        1 | Sam  | April |
+----------+------+-------+
5 rows in set (0.00 sec)
4
  • Does not work always return 1 Joe and April should return 2 Dec 26, 2013 at 5:47
  • I hope you are intended to return 1 as in your example you said that Joe April should be 1 but Joe April are two times in table. Dec 26, 2013 at 5:48
  • Joe April should be 2 Dec 26, 2013 at 6:32
  • Updated My Answer. Dec 26, 2013 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.