Seena Blace <[EMAIL PROTECTED]> wrote on 05/16/2005 10:08:15 AM:
> Any suggestion pl?
>
> Seena Blace <[EMAIL PROTECTED]> wrote:hi,
> here is table description
> report1
>
> +-------------------------+------------------+------+-----
> +---------------------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------------------+------------------+------+-----
> +---------------------+----------------+
> | id | int(10) unsigned | | PRI | NULL | auto_increment |
> | host_id | int(10) unsigned | | MUL | 0 | |
> | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | |
> | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | |
> | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | |
> | time | datetime | | MUL | 0000-00-00 00:00:00 | |
> | detected_spam | int(10) unsigned | | | 0 | |
> | detected_virus | int(10) unsigned | | | 0 | |
> | processed | int(10) unsigned | | | 0 | |
> | allowed | int(10) unsigned | | | 0 | |
> | suspected | int(10) unsigned | | | 0 | |
> | blocked | int(10) unsigned | | | 0 | |
> | spam | int(10) unsigned | | | 0 | |
> | virus | int(10) unsigned | | | 0 | |
>
>
> I WANT REPORT LIKE FOLLOWINGS
>
> date sender processed spam suspected
>
>
> I want top 10 spam sender each day.
>
> QUery i'm using
> select date_format(time,'%Y-%d-%m'),report_sender_domain_id,
> processed ,spam from report1
> order by spam desc ,report_sender_domain_id,date_format(time,'%Y-%
> d-%m') limit 10;
>
>
>
> Please suggest.
> thanks
>
It was the weekend. Not everyone lurks on their days off.
Try this
select date_format(time,'%Y-%d-%m')
,report_sender_domain_id
,processed
,spam
,suspected
from report1
order by spam desc
,report_sender_domain_id
,date_format(time,'%Y-%d-%m')
limit 10;
or if there is more than one entry per spammer per day
SELECT date_format(time,'%Y-%d-%m')
,report_sender_domain_id
,sum(processed) as processed
,sum(spam) as spam
,sum(suspected) as suspected
FROM report1
GROUP BY date_format(time,'%Y-%d-%m')
,report_sender_domain_id
ORDER BY spam desc
,report_sender_domain_id
,date_format(time,'%Y-%d-%m')
LIMIT 10;
That will give you their total stats for each day.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
