Hi all,

Iam having the one table name called AccessDetails and data inside that
tables is following,

Date Time UserName SiteName
ScanType Status Virus_Category

| 2008-04-16 | 13:05:31 | 172.16.1.22 | - | www.veer.com
|C | A | unclassified |
| 2008-04-16 | 13:05:31 | 172.16.1.52 | - | blogactiv.eu
|C | O | unclassified |
| 2008-04-16 | 13:05:32 | 172.16.1.22 | - | www.veer.com
|V | A | Internet |
| 2008-04-16 | 13:05:32 | 172.16.1.52 | - |
www.verylowsodium.com |C | D | unclassified |
| 2008-04-16 | 13:05:32 | 172.16.1.52 | - | blogactiv.eu
|V | A | unclassified |


In that , I need to calculate the number of total sites , number of
total Accessed Sites,number of total Denied Sites and number of total
Overriden Sites based on the particular Virus_Category,UserName,Date


How can form the query to achieve that??

I have used the following query but the total site is not correctly
displayed..


select
count(a.UserName),sum(b.totalsites),sum(a.Allow),s um(a.Denied),sum(a.Over),sum(b.totalconn) from (select a.UserName,sum(a.Allow) as Allow,sum(a.Denied) as Denied,sum(a.Over) as Over from (select UserName,case Status when 'A' then count(distinct SiteName) else 0 END as Allow ,case Status when 'D' then count(distinct SiteName) else 0 END as Denied,case Status when 'O' then count(distinct SiteName) else 0 END as Over from AccessDetails where Virus_category = 'unclassified ' and Date<='2008-04-16' and Date>='2008-04-16' and ScanType='C' group by UserName, Status) a group by a.UserName) a left join (select UserName,count(distinct SiteName)as totalsites, count(Time)as totalconn from AccessDetails where Virus_category = 'unclassified ' and Date<='2008-04-16' and Date>='2008-04-16' and ScanType='C' and Virus_category <> '-' and UserName <> '-' group by UserName)b on a.UserName=b.UserName where b.totalsites is not null



Thanks In Advance ,

On Wed, Apr 16, 2008 at 4:35 AM, sivasakthi <msivasakthi@gmail.com> wrote:
> Hi all,
>
> Iam having the one table name called AccessDetails and data inside that
> tables is following,
>
[snip=schema]
>
> In that , I need to calculate the number of total sites , number of
> total Accessed Sites,number of total Denied Sites and number of total
> Overriden Sites based on the particular Virus_Category,UserName,Date
>
>
> How can form the query to achieve that??
>
> I have used the following query but the total site is not correctly
> displayed..

You may want to look into the ROLLUP modifier. Here's the manual entry:
http://dev.mysql.com/doc/refman/5.0/...modifiers.html

--
</Daniel P. Brown>
Ask me about:
Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo.,
and shared hosting starting @ $2.50/mo.
Unmanaged, managed, and fully-managed!