CODEWITHSUNDEEP
sqlsqlite
CruelEngine
CruelEngine

Reputation: 2841

Using a subquery as reference to generate multiple result in same query

I'm using sqlite to store some data and i have a Subquery that i want to reuse to deduce multiple results and the Subquery goes like this : 

select * from s_stats where datetime(start_time) > datetime('now','localtime','-3 days') group by src_ip,src_port,dest_ip,dest_port order by start_time desc

I want to re use the above query to generate multiple filtered data in the same query .

One Result is done by this :

 select start_time,action,count(*) from (select * from s_stats where datetime(start_time) > datetime('now','localtime','-3 days') group by src_ip,src_port,dest_ip,dest_port order by start_time desc) where action='BLOCKED' group by action,start_time order by start_time desc

I also want to do : 

select start_time,action,count(*) from (select * from s_stats where datetime(start_time) > datetime('now','localtime','-3 days') group by src_ip,src_port,dest_ip,dest_port order by start_time desc) group by start_time order by start_time desc

Is there any way to combine both the query into one single query by using the subquery as some variable ?

Thanks

Upvotes: 0

Views: 41

Answers (1)

Nick
Nick

Reputation: 147196

You can use conditional aggregation to get both counts in one query:

select start_time,
       action,
       count(case when action = 'BLOCKED' then 1 end) as blocked,
       count(*) as total
from (select * 
      from s_stats 
      where datetime(start_time) > datetime('now','localtime','-3 days') 
      group by src_ip,src_port,dest_ip,dest_port 
      order by start_time desc)
group by start_time 
order by start_time desc

Upvotes: 2

Related Questions