0

I have this query that i'm using to pull all the user accounts that login for a given day

    SELECT DISTINCT
  users_logins.user_id,
  users.first_name,
  users.last_name,
  users.email,
  CONVERT_TZ(users_logins.login_time,'+00:00','-07:00') AS 'LoginTime'
FROM users
  LEFT OUTER JOIN users_logins
    ON users.id = users_logins.user_id
WHERE 
 CONVERT_TZ(users_logins.login_time,'+00:00','-07:00')
 BETWEEN '2021-09-13 00:00:00' 
        AND '2021-09-13 23:59:00' AND
      users.third_party = '0' 
      GROUP BY 1
  ORDER BY login_time ASC

And this gives me list of distinct users who login at least 1 time a day, what i want to do in one sql if possible is have this give me same daily output for 1 month. So list all the users for say Aug 1 , 2, 3 - Aug 31, in one output. Im sure the SQL experts here know simple way to do it :) Thanks for any help

kyuzon
  • 35
  • 6
  • Your existing query is against the sql standards and would fail even on mysql if only full group by sql mode was set (it is set by default for several years now). If you were to get the day of their login along with user name and email (these are functionally dependent oon user id) and group by on user id + day of login, that would work. Distinct is not needed. – Shadow Sep 16 '21 at 17:25
  • not sure what you mean as my query above does work in my database. but i will look at the idea of grouping by user id and day. the issue i have is users login multiple times a day so i just want their info if they login at least 1 time. but i will look at trying this, if you had any code example would be helpful thx – kyuzon Sep 16 '21 at 17:41
  • See the following answer to understand what's wrong with your query in the question: https://stackoverflow.com/a/41887524/5389997 – Shadow Sep 16 '21 at 20:22

1 Answers1

0

ok using shadows hint i was able to get my query to output as needed, thx

   SELECT 
  users_logins.user_id,
  users.first_name,
  users.last_name,
  users.email,
  date(users_logins.login_time) AS `Login Day`,
  CONVERT_TZ(users_logins.login_time,'+00:00','-07:00') AS 'LoginTime'
FROM users
  LEFT OUTER JOIN users_logins
    ON users.id = users_logins.user_id
WHERE 
 CONVERT_TZ(users_logins.login_time,'+00:00','-07:00')
 BETWEEN '2021-08-01' 
        AND '2021-08-31' AND
      users.third_party = '0' 
      GROUP BY 5, 1
  ORDER BY login_time ASC
kyuzon
  • 35
  • 6