Mysql Question posted in
The official MySQL documentation can be found here.

mysql – how to get date of first user ticket

I have a tickets table with this info:

ticket_id    |    user_id   |   used_at
----------------------------------------
xxx          |      xxx     |   datetime
xxx          |      xxx     |   datetime
xxx          |      xxx     |   datetime

I want to get a list of all users who has ONLY ONE TICKET and USED_AT date of it.

My current select works to get user and their first ticket date.

SELECT
  washtickets.id,
  washtickets.user_id,
  washtickets.used_at
FROM
  washtickets,
  (SELECT user_id, min(used_at) AS used_at FROM washtickets GROUP BY user_id) max_user
WHERE
  washtickets.deleted_at IS NULL
  AND washtickets.user_id = max_user.user_id
  AND washtickets.used_at = max_user.used_at
ORDER BY
  used_at ASC;

But I don’t know to add COUNT(*) to filter only users who has ONLY ONE used ticket.

Any help?

Tags:

2

Answers


  1. 0

    First JOINS are SQL Standard for over 20 years, so please use them

    Count is an aggregation function, so you need a GROUP BY

    All columns that are not in the GROUP BY must have a aggregation function, as you only want people with one ticket, so the id should be have one numbers so the aggregation function doesn’t matter.

    SELECT
  MIN(w.id),
  w.user_id,
  w.used_at
  
FROM
  washtickets w JOIN
  (SELECT user_id, min(used_at) AS used_at FROM washtickets GROUP BY user_id) max_user
ON
  w.deleted_at IS NULL
  AND w.user_id = max_user.user_id
  AND w.used_at = max_user.used_at
GROUP BY w.user_id, w.used_at
HAVING COUNT(*) = 1
ORDER BY
  used_at ASC;
    Login or Signup to reply.
  2. 0

    Maybe I am misunderstanding what you are trying to do, but I do not see the need for the subquery.

    It is not completely clear what you want as you start off by saying –

    all users who has ONLY ONE TICKET and USED_AT date of it.

    but later say –

    only users who has ONLY ONE used ticket.

    Either way I cannot see the need (or benefit) in the subquery.

    This query returns all users with only one ticket which is not deleted and has a used_at value:

    SELECT
  MIN(ticket_id) AS ticket_id,
  user_id,
  MIN(used_at) AS used_at
FROM washtickets
WHERE deleted_at IS NULL
AND used_at IS NOT NULL
GROUP BY user_id
HAVING COUNT(*) = 1;

    Here’s a db<>fiddle.

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

Back To Top
Search