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

get the total count but exclude certain condition – Mysql

Hello I had this table:

id | user_id | status
1  |  34     |  x
2  |  35     |  x
3  |  42     |  x
4  |  42     |  y

My goal is to count the data with X status except if the user has a another data with Y status, it will exclude in the count. So instead of 3, it will only count 2 since the 3rd row has another data which is the 4th row with y status.

SELECT * FROM logs
AND user_id NOT IN (SELECT user_id FROM logs WHERE status = 'y')
GROUP BY user_id;
Tags:

3

Answers


  1. 0

    We can try the following aggregation approach:

    SELECT COUNT(*) AS cnt
FROM
(
    SELECT user_id
    FROM logs
    GROUP BY user_id
    HAVING MIN(status) = MAX(status) AND
           MIN(status) = 'x'
) t;

    The above logic only counts a user having one or more records only having x status.

    Login or Signup to reply.
  2. 0

    You can do it this way, I only modify a bit on your sql

    SELECT COUNT(*) FROM (
    SELECT u_id FROM tbl WHERE u_id NOT IN
        (SELECT u_id FROM tbl WHERE status = 'y')
    GROUP BY u_id
) as t
    Login or Signup to reply.
  3. 0

    You can use inner join:

    SELECT
    count(t1.id) AS `cnt`
FROM
    `test` AS t1,
    `test` AS t2
WHERE
    t2.`status`='y'
    && t1.`user_id` != t2.`user_id`;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search