phpmyadmin Question posted in
The official documentation can be found here.

Multiple where clause in sql statement? – Phpmyadmin

I have a simple sessions table which looks like this: 

   sid  watchtime
    1   01:07:00
    2   01:07:00
    3   01:34:00
    4   01:36:00
    5   01:36:00
    6   01:59:00
    7   01:59:00
    8   00:00:00
    9   00:00:00
    10  03:42:00
    11  00:16:00
    12  00:49:00
    13  00:55:00
    14  00:00:00
    15  00:05:00
    16  00:05:00

Now, I want to display count for watchtime >=5 and watchtime <5. I tried the following solution:

SELECT count(watchtime) as lessthan_five FROM sessions 
WHERE watchtime <=5 
AND count(watchtime) as morethan_five 
         from sessions WHERE watchtime >5";

I am getting the following error:

1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘as morethanfive from sessions where WHERE watchtime >=5″‘ at line 1

What am I doing wrong in my code?

Tags:

3

Answers


  1. 0

    You may try this

       SELECT watchtime, count(watchtime) as lessthan_five FROM sessions 
   WHERE watchtime <=5 
   AND watchtime in ( select watchtime as morethan_five 
     from sessions group by `category_id` having `watchtime` >5);
    Login or Signup to reply.
  2. 0

    You can use conditional aggregation to get the result you want, making use of MySQL treating boolean values as 1 or 0 in a numeric context:

    SELECT SUM(watchtime <= '00:05:00') AS lessthan_five,
       SUM(watchtime >  '00:05:00') AS morethan_five
FROM sessions

    Output:

    lessthan_five   morethan_five
5               11

    Demo on dbfiddle

    Login or Signup to reply.
  3. 0

    If you want rows with value more than five minutes:

    SELECT COUNT(*) FROM sessions WHERE watchtime >= '00:05:00'
SELECT COUNT(*) FROM sessions WHERE watchtime < '00:05:00'

    If you want number of rows with minutes past the hour:

    SELECT COUNT(*) FROM sessions WHERE MINUTE(watchtime) >= 5
SELECT COUNT(*) FROM sessions WHERE MINUTE(watchtime) < 5

    Also, this will give you the count for all watchtime, where the count <> 5:

    SELECT watchtime, COUNT(*) as Num
FROM sessions 
GROUP BY watchtime
HAVING COUNT(*) <> 5

    You can edit the having-clause as you wish.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search