-2

like 

select ho from (select sourceaddress,count(sourceaddress) as 
src,hour(eventtime) as ho 
from default.fullandfinal 
where sourceaddress='0.0.0.0' and  eventtime between '2019-05-11 00:00:00' and 
'2019-05-11 19:59:59'  
group by sourceaddress,hour(eventtime) order by sourceaddress,ho) t where 
src=28350;

the output of this query is 11 and i want to use this output in my nxt query which is 

select sourceaddress,destinationaddress,destinationport,name,count(*) as count  
from fullandfinal 
where eventtime like "11%" and sourceaddress='0.0.0.0'  
group by sourceaddress,destinationaddress,destinationport,name 
order by count desc limit 5;

i want to write single query for this is it possible ?

vishwajeet
  • 83
  • 1
  • 9

1 Answers1

0

Considering MySQL - Yes possible. You have to use the first query as Sub Query in the second Query. The query structure will be something like this-

SELECT *,
(SELECT ID FROM TABLE_1 WHERE ....) AS [From Other Query] -- In the selection part 
FROM TABLE_2
WHERE TABLE_2.ID = (SELECT ID FROM TABLE_1 WHERE ....) -- In Where condition

For both above case, you have to make sure your sub query returns one single value that is like 11 you mentioned.

Try This-

SELECT sourceaddress,
destinationaddress,
destinationport,
[name],
COUNT(*) as [count]  
FROM fullandfinal 
WHERE 
eventtime LIKE
(
    SELECT ho FROM 
    (
        SELECT sourceaddress,
        COUNT(sourceaddress) AS src,
        HOUR(eventtime) AS ho 
        FROM DEFAULT.fullandfinal 
        WHERE sourceaddress='0.0.0.0' 
        AND  eventtime BETWEEN '2019-05-11 00:00:00' AND '2019-05-11 19:59:59'  
        GROUP BY sourceaddress,
        HOUR(eventtime) 
        ORDER BY sourceaddress,ho
    ) t
    WHERE src=28350
) + '%' 
AND sourceaddress='0.0.0.0'  
GROUP BY sourceaddress,destinationaddress,destinationport,name 
ORDER BY COUNT(*) DESC 
limit 5;
mkRabbani
  • 16,295
  • 2
  • 15
  • 24
  • There is only one table @mkRabbani – vishwajeet May 16 '19 at 05:24
  • No issue, both case use table reference TABLE_1. I just shown the structure for your reference. – mkRabbani May 16 '19 at 05:26
  • i m not getting what you are tying to say please explain in brief . Or arrange the above query as per our requirement. @mkRabbani – vishwajeet May 16 '19 at 06:10
  • Where you wants to use 11 (Output from query 1) in Query 2? – mkRabbani May 16 '19 at 06:18
  • One clarification, I am talking about MySQL database that seems initially tagged in your post. – mkRabbani May 16 '19 at 06:20
  • I updated the query please check this. I want like that..@mkRabbani – vishwajeet May 16 '19 at 06:36
  • @vishwajeet Check the added script work for you or not. – mkRabbani May 16 '19 at 06:46
  • SELECT sourceaddress,destinationaddress,destinationport,name,COUNT(*) as count1 FROM fullandfinal WHERE eventtime LIKE "2019-05-11 (SELECT ho FROM (SELECT sourceaddress,COUNT(sourceaddress) AS src,HOUR(eventtime) AS ho FROM fullandfinal WHERE sourceaddress='192.168.6.2' and eventtime BETWEEN '2019-05-11 00:00:00' and '2019-05-11 19:59:59' group by sourceaddress,HOUR(eventtime) ORDER BY sourceaddress,ho) t WHERE src=28350)%" and sourceaddress='192.168.6.2' group by sourceaddress,destinationaddress,destinationport,name limit 5..it is showing none output @mkRabbani – vishwajeet May 16 '19 at 07:29