Beginner MySQL: Creating a table of User Logins per Day, having trouble grouping them-CodePudding

Hello and thanks in advance for any answers, I'm a beginner & I've been stuck on this issue for a day now. I have the query below that is giving me an error at the AND, saying my <= sign is not valid there. I am not sure how to correct this.

-- User Logins Per Day
SELECT
  DATE(created_time_stamp),
  security_profile_id AS Name,
  COUNT(*) AS logins,
  CASE
    WHEN COUNT(security_profile_id) <= 1
    THEN “one_session”
    WHEN COUNT(security_profile_id) > 1 AND <= 3
    THEN “two_to_three_sessions”
    ELSE “four_plus_sessions”
END AS sessions_summary
FROM session
WHERE security_profile_id IS NOT NULL
GROUP BY
  DATE(created_time_stamp),
  Name
ORDER BY
  DATE(created_time_stamp),
  Name

CodePudding user response：

You need to specify a value to compare with 3.

WHEN COUNT(security_profile_id) > 1 AND COUNT(security_profile_id) <= 3

But there's no need to test > 1 in the first place, since the first WHEN condition handles all the values <=1. So just change it to:

WHEN COUNT(security_profile_id) <= 3

And more generally, you can use BETWEEN if you want to test if a number is between two values. It's inclusive, so the equivalent expression would be

WHEN COUNT(security_profile_id) BETWEEN 2 AND 3

CodePudding user response：

A valid expression is expected after the AND you're referring to, <= 3 is not valid, you probably want to write WHEN COUNT(security_profile_id) > 1 AND COUNT(security_profile_id) <= 3.

Therefore:

-- User Logins Per Day
SELECT
  DATE(created_time_stamp),
  security_profile_id AS Name,
  COUNT(*) AS logins,
  CASE
    WHEN COUNT(security_profile_id) <= 1
    THEN “one_session”
    WHEN COUNT(security_profile_id) > 1 AND COUNT(security_profile_id) <= 3
    THEN “two_to_three_sessions”
    ELSE “four_plus_sessions”
END AS sessions_summary
FROM session
WHERE security_profile_id IS NOT NULL
GROUP BY
  DATE(created_time_stamp),
  Name
ORDER BY
  DATE(created_time_stamp),
  Name