combine two queries result and divide-CodePudding

I have 2 queries: the first query is to get the count of the number of records that falls between a particular date and time grouped by name. The other query is to count the other records that fall outside the date and time grouped by name. After that, I would like to get the percentage of the occurrence.

Query 1:

select name, count(*)
from table 1
where Start_datetime <= time_data => End_datetime
group by name;

Query 2

select name, count(*)
from table 2
where time_data => Start_datetime
group by name;

I've tried combining both queries:

SELECT((    select name, count(*)
    from table 1
    where Start_datetime <= time_data => End_datetime
    group by name)/(select name, count(*)
    from table 1
    where time_data => Start_datetime
    group by name));

But I get this error: cub-select returns 2 columns- expected one

How can I do this?

CodePudding user response：

select (a.firstCount / b.secondCount ) * 100 as totalCount 
from 
   (select name, count(*) as firstCount 
    from table1 
    where Start_datetime <= time_data => End_datetime 
    group by name
   ) a,
   (select name, count(*) as secondCount 
    from table2 
    where time_data => Start_datetime 
    group by name
   ) b
where a.name = b.name

CodePudding user response：

You can join your queries

SELECT t1.name,count_1 /count_2
FROM
(select name, count(*) count_1
from table_1
where Start_datetime <= time_data => End_datetime
group by name) t1
JOIN (select name, count(*) count_2
from table_2
where time_data => Start_datetime
group by name) t2 ON t1.name = t2.name

or use a szbselct this way

SELECT  select name, count(*) /(select name, count(*)
    from table_2
    where time_data => Start_datetime AND  name = table1.name)
FROM table_1
group by name