MySQL change virtual column based on WHERE clause

Question

Is it possible to change a virtual column value based on a where clause?

I have this table:

[computername] [computerlocation]  [starttime]  [endtime]
computer,      siteA,              1457537657,  1457532657
computer2,     siteB,              1457547657,  1457546657
computer3,     siteB,              1457237657,  14575237657

I'd like to see how many computers there are, at a given site and in a given time frame, the query I currently use is:

select count(*), computerlocation 
from table 
where site like "site%" 
  and starttime <= "1457532657" and endtime >= "1457532657" 
group by computerlocation

However, at the moment I have to run this query hundreds of times to create a graph which displays over a period of time how many computers there are.

Is it possible to make something like this:

select count(*), computerlocation, "null" as time 
from table 
where site like "site%" 
 and ( (starttime <= "1457532657" and endtime >= "1457532657" as time="timeA") 
    OR (starttime <= "1457532357" and endtime >= "1457532357" as time="timeB") 
    OR (starttime <= "1457532651" and endtime >= "1457532651" as time="timeC") 
     ) 
group by time, computerlocation

score 0 · Accepted Answer · answered Mar 09 '16 at 19:03

You use a CASE expresion

First you create your virtual column, and then you perform the group by

SELECT time_group, computerlocation, count(*)
FROM (
        SELECT computerlocation,
                CASE WHEN (starttime <= '1457532657' and endtime >= '1457532657') THEN 'timeA'
                     WHEN (starttime <= '1457532357' and endtime >= '1457532357') THEN 'timeB'
                     WHEN (starttime <= '1457532651' and endtime >= '1457532651') THEN 'timeC'
                     ELSE 'N/A'
                END as time_group
        from table 
        where site like 'site%'
     ) T
GROUP BY time_group, computerlocation

btw double quotes mean "fieldname" single quotes mean string 'string'

You should check BETWEEN comparator, should be write as

WHERE '1457532657' BETWEEN starttime AND endtime

Exactly what I'm looking for ! thanks ! I tried using BETWEEN, but in my tests I found that using >= and <= was faster? — Josh Budd, Mar 09 '16 at 19:09

MySQL change virtual column based on WHERE clause

1 Answers1