MySQL Help – Combining 2 MySQL Selects – Result in 1

MySQLselect

I have two MySQL statements (see below), I would like to combine them together so if they both result in a 1 then the end result will be 1. I'm not sure how to construct this and was hoping for some help.

select count(*)
from monitor
where name='job_starttime' and value < ( UNIX_TIMESTAMP( ) -600)

select count(*)
from monitor
where name='job_active' and value = 0

So for example I would like when both statements are true to result in a value of 1, if 1 or none are true it results in a 0.

I nearly achieved it with the below SQL (except it's giving a result for each entry in the table) 🙁

select
case
when (select count(*) from monitor
where exists (select * from monitor where name = 'job1_active' AND value = 1)
and exists (select * from monitor where name = 'job2_active' AND value = 1)) > 1
then 1
else 0
end
from monitor

Best Answer

(I am assuming you want the result in a WHERE or some other place for testing?)

This is faster, but is equivalent to COUNT(*) >= 1.

SELECT ...
    WHERE EXISTS ( SELECT * FROM monitor
                   WHERE name = 'job_starttime' AND value < ... )
      AND EXISTS ( SELECT * FROM monitor
                   WHERE name = 'job_active' AND value = 0 )

Otherwise:

SELECT ...
    WHERE ( SELECT COUNT(*) ... ) = 1
      AND ( SELECT COUNT(*) ... ) = 1

Another tip: false == 0; true == 1.

Related Solutions

How to select data and the total to cursors

An alternative to msi77's solution is the use of analytical functions. This might be a bit more efficient but you should run an explain plan to find out.

SELECT id,
       name, 
       count(*) over () as total_count
FROM your_table
WHERE name = 'AA'

Mysql – sum interval dates

You could merge overlapping intervals and count the hours from there (untested):

select name, min(start), end 
from (
    select x.name, x.start, min(y.end) as end 
    from t as x 
    join t as y 
        on x.name = y.name 
       and x.start <= y.end 
       and not exists (
           select 1 
           from t as z 
           where y.name = z.name 
             and y.end >= z.start 
             and y.end < z.end
       ) 
    where not exists (
        select 1 
        from t as u 
        where x.name = u.name 
          and x.start > u.start 
          and x.start <= u.start
    ) 
    group by x.name, x.start
) as v group by name, end;

Best Answer

Related Solutions

How to select data and the total to cursors

Mysql – sum interval dates

Related Question