tags:

views:

34

answers:

2
select * from hari;
+------+------------+-------+
| id   | mydate     | email |
+------+------------+-------+
|    1 | 2009-10-10 | 1111  | 
|    1 | 2009-10-10 | 1111  | 
|    1 | 2009-10-10 | 2222  | 
|    2 | 2010-11-11 | 3333  | 
|    2 | 2010-11-11 | 3333  | 
+------+------------+-------+
5 rows in set (0.01 sec)

After grouping by id, mydate and email there will be only 2 records mathcing id = 1 How do I achive this?

Someone has given me the following query where the count for the first ID is 2 (correct) But I find this query very confusing and guess there must be some better way.

mysql>SELECT
count(sel2.refid2 ) as recCount,
refid2, recDate2
FROM
(SELECT id AS refid2, mydate AS recDate2
FROM hari AS ol_Email1
GROUP BY ol_Email1.id, ol_Email1.mydate, ol_Email1.email 
) AS sel2
GROUP BY sel2.refid2
+----------+--------+------------+
| recCount | refid2 | recDate2   |
+----------+--------+------------+
|        2 |      1 | 2009-10-10 | 
|        1 |      2 | 2010-11-11 | 
+----------+--------+------------+
2 rows in set (0.28 sec)

CREATE TABLE `hari` (
  `id` int(11) default NULL,
  `mydate` date default NULL,
  `email` varchar(100) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `hari` VALUES (1,'2009-10-10','1111'),(1,'2009-10-10','1111'),(1,'2009-10-10','2222'),(2,'2010-11-11','3333'),(2,'2010-11-11','3333');
+1  A: 
SELECT * FROM hari WHERE id = 1 GROUP BY email 
Flakron Bytyqi
I need the count of 2 in the output
shantanuo
SELECT COUNT(*), id, mydate, email FROM hari WHERE id = 1 GROUP BY email
Flakron Bytyqi
A: 

Something like...

SELECT *,
       COUNT(*)
    FROM hari
    GROUP BY id, mydate, email;

(I'm not quite sure which columns you want to group by)

Brian Hooper