CODEWITHSUNDEEP
mysql
soumen
soumen

Reputation: 51

max count for count(*)

My table structure is 

| Field      | Type      | Null | Key | Default           | Extra |
| uid        | char(255) | NO   |     |                   |       |
| lid        | char(255) | NO   | MUL |                   |       |
| ip_address | char(15)  | NO   |     |                   |       |
| user_agent | char(255) | YES  |     | NULL              |       |
| open_date  | timestamp | NO   | MUL | CURRENT_TIMESTAMP |       |
| referrer   | char(255) | YES  |     | NULL              |       |
| environ    | text      | YES  |     | NULL              |       |
| country    | char(255) | NO   | MUL |                   |       |

I want to query for max clicks recored in a particular day of a month

Query

select count(open_date) as c,day(open_date) as d
from link_click 
where month(open_date)="01" and year(open_date)="2011" 
group by d
having c =MAX(c);

but it's resulting empty set.

Pls. suggest.

Upvotes: 5

Views: 1066

Answers (2)

awe
awe

Reputation: 22442

This might work:

select * from (
  select count(open_date) as c,day(open_date) as d
  from link_click 
  where month(open_date)="01" and year(open_date)="2011" 
  group by d)
having c =MAX(c);

Upvotes: 0

JohnC
JohnC

Reputation: 499

Is this what you are after?

select count(open_date) as c,day(open_date) as d
from link_click 
where month(open_date)="01" and year(open_date)="2011" 
group by d
order by c desc
limit 1;

Upvotes: 3

Related Questions