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),sum(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 ,
