CODEWITHSUNDEEP
mysqlsql
tman
tman

Reputation: 425

How to combine two sql queries from same table

I have the following (example) data in a table

Value, Date, Device

I use the data to do simple SLA calculations, i.e. how many times during business hours was the data below a certain threshold.

Currently I run the following two queries like the following

select count(*), DATE(times) from SLA where device='some-dev' and DATE(times) between '2013-05-01' and '2013-05-31' and TIME(times) between '06:00:00' and '18:00:00' and value <150 group by DATE(times);

select count(*), DATE(times) from SLA where device='some-dev' and DATE(times) between '2013-05-01' and '2013-05-31' and TIME(times) between '06:00:00' and '18:00:00' group by DATE(times);

So two queries, one without a value < number and one with.

A few questions, is there a way to get these both into one query, so I could see a count of total data points and data points that were less than threshold value? Basically return three columns, one with count, one with count below thresh, and date

Beyond that, is there an easy way to tell mysql to ignore weekends when returning the data?

Thanks,

Upvotes: 2

Views: 4651

Answers (5)

user8833945
user8833945

Reputation: 11

select count(*), DATE(time) from SLA where (device='some-dev' and DATE(time) between '2013-05-01' and '2013-05-31' and TIME(times) between '06:00:00' and '18:00:00' and value <150 group by DATE(time)) or (device='some-dev' and DATE(times) between '2013-05-01' and '2013-05-31' and TIME(time) between '06:00:00' and '18:00:00' group by DATE(time));

Upvotes: 1

Vman
Vman

Reputation: 3136

I'm more ms-sql, but presumably you can do something like this:

select count(*), DATE(times), SUM(CASE WHEN value <150  THEN 1 ELSE 0 END)
from SLA
where device='some-dev'
and DATE(times) between '2013-05-01' and '2013-05-31'
and TIME(times) between '06:00:00' and '18:00:00'
group by DATE(times);

in mysql.

Upvotes: 1

david a.
david a.

Reputation: 5291

If, as you wrote, you want three columns (two counts and a date), use subselect rather than union:

select 
(select count(*) from SLA where device='some-dev' and DATE(times) = DATE(s.times) and  TIME(times) between '06:00:00' and '18:00:00' and value <150),
count(*), 
DATE(s.times) 
from SLA s
where device='some-dev' and DATE(times) between '2013-05-01' and '2013-05-31' and TIME(times) between '06:00:00' and '18:00:00' group by DATE(times)

I didn't test the query above, so it might require a little tweaking, however see a simplified working example.

EDIT: Just updated the linked fiddle (once more) with a (even) more meaningful query.

Upvotes: 2

Gogutz
Gogutz

Reputation: 2055

How about:

select count(*), DATE(times) from SLA 
where (device='some-dev' and DATE(times) between '2013-05-01' and '2013-05-31' and TIME(times) between '06:00:00' and '18:00:00' and value <150 group by DATE(times))
or (device='some-dev' and DATE(times) between '2013-05-01' and '2013-05-31' and TIME(times) between '06:00:00' and '18:00:00' group by DATE(times));

Upvotes: 0

Maxim Kolesnikov
Maxim Kolesnikov

Reputation: 5145

Use UNION operator.

Updated query will look like so:

(select count(*), DATE(times) from SLA where device='some-dev' and DATE(times) between '2013-05-01' and '2013-05-31' and TIME(times) between '06:00:00' and '18:00:00' and value <150 group by DATE(times))
UNION
(select count(*), DATE(times) from SLA where device='some-dev' and DATE(times) between '2013-05-01' and '2013-05-31' and TIME(times) between '06:00:00' and '18:00:00' group by DATE(times));

Upvotes: 3

Related Questions