0

Hobby databasist here.

In my database, the statement below will output the number of posts per hour which discussed subject foo.

select 
    count(postid) 
from 
    myTable 
where 
    (sub like ("%foo%") or comment like ("%foo%")) 
    and replies != 'NULL'  
group by 
    time div 3600 
order by time div 3600 asc;

Output: [time div 3600, foo count]

this statement below would output the number of posts per hour which discussed subject bar.

select 
    count(postid) 
from 
    myTable 
where 
    (sub like ("%bar%") or comment like ("%bar%")) 
    and replies != 'NULL'  
group 
    by time div 3600 
order by 
    time div 3600 asc;

Output: [time div 3600, bar count]

How to I combine the output such that:

Output: [Time div 3600, foo count, bar count]

  • this selects are not correct – McNets Nov 26 '16 at 1:55
  • Don't you also want time div 3600 in the SELECT? Or maybe (time div 3600) * 3600, or even FROM_UNIXTIME((time div 3600) * 3600) ?? – Rick James Nov 28 '16 at 18:54
0

You can combine them like this:

select 
    count(CASE WHEN sub like '%bar%' or comment like '%bar%' THEN postid ELSE NULL END) AS count_bar, 
    count(CASE WHEN sub like '%foo%' or comment like '%foo%' THEN postid ELSE NULL END) AS count_foo
from 
    myTable 
where 
    (sub like '%bar%' or comment like '%bar%'
     or sub like '%foo%' or comment like '%foo%') 
    and replies IS NOT NULL
group 
    by time div 3600 
order by 
    time div 3600 asc;

Note that I also change and replies IS NOT NULL, assuming you have rows that are actually NULL. With the quotes you used, you treated it as a string, but NULL is really nothing. You can't compare it like other datatypes, you have to use the special IS NULL or IS NOT NULL comparison.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.