2

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,

Improve this question
3
  • no easy way to ignore weekends. Commented Jun 13, 2013 at 16:00
  • 1
    You can ignore weekends with this condition in WHERE clause: DAYOFWEEK(times)<6. More about DAYOFWEEK function avalible here. Commented Jun 13, 2013 at 16:04
  • The first query is sub set of second. This mean execute only second, and you will get the result. Commented Jun 13, 2013 at 16:48

5 Answers 5

Reset to default
3

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));
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I tried that, but it only returns two columns not three seperate. DO I need to define the count(*) as a different column in the second query?
The questioner asked for a query to produce three columns (two counts for a particular date and the date). Union won't do that.
2

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.

Improve this answer

4 Comments

OK, with the amount of records I have, this query is taking a very long time to run, as in over 10 minutes and hasn't returned. Any ideas how to make the above run faster? Thanks also didn't know about the sqlfiddle site, nice.
Update, it took 48 minutes to run. If I just run the two queries themselves, i.e. return all records below threshold of some number, and all records regardless of threshold, then they return in about 16 seconds each. So this is a case where combining the queries is very expensive...
yes, the subselect query is run each once for each row returned by the main query. depending on number of records in your table, that can mutliply the time spent there a lot. At the moment, I can't think of an improvement (but sure there are some tuneups possible).
Ok, so adding an index or composite index won't greatly speed this up?
1

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.

Improve this answer

Comments

1 
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));
Improve this answer

Comments

0

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));
Improve this answer

2 Comments

this will work, but the use of an 'OR' usually prevents an index from being used. in general, two index using selects that are unioned together will run faster than table scanning 'OR' based query. Think of a phone book search: give me all the rows that are Jones or Smith? how would it run, scan the entire book for Jones or Smith. now try give me all the rows that are Jones and combine them with all the rows for Smith. it would use the index to go to Jones and return all of them and then it would use the index to get all the Smith rows and combine them together,
I didn't knew that and now that i think about it, it makes sense. Thank you for your explanation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.