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

  • I hope count of Joe April should be 2. – Abdul Manaf Dec 26 '13 at 5:29
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)
  • Does not work always return 1 Joe and April should return 2 – stackoverflow Dec 26 '13 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. – Abdul Manaf Dec 26 '13 at 5:48
  • Joe April should be 2 – stackoverflow Dec 26 '13 at 6:32
  • Updated My Answer. – Abdul Manaf Dec 26 '13 at 6:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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