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

Mysql – How to count number of times a “group” had more than 1 value in another field

My unique SQL use case is as follows:

I have userId, isCondition, timestamp

I want number times all results per userId has timestamp always containing a starting substring and isCondition value has more than 1 distinct value amongst those results

Example:

UserID1, timestamp*, 0

UserID1, timestamp*, 1

UserID2, timestamp*, 0

UserID2, timestamp*, 0

Returns 1 because only 1 group of results for userID1 had isCondition with multiple distinct values. Had the 3rd or 4th row had distinct values for column 3, the result would have been 2 indicating 2 users demonstrating distinct values for column 3 for the same global timestamp substring value

Sample DB creation:

CREATE TABLE test (
  user varchar(255),
  timestp varchar(255),
  isCond int
);
INSERT INTO test (user, timestp, isCond) VALUES ("user1", "2024-02-08-11", 0);
INSERT INTO test (user, timestp, isCond) VALUES ("user1", "2024-02-08-12", 1);
INSERT INTO test (user, timestp, isCond) VALUES ("user2", "2024-02-08-13", 0);
INSERT INTO test (user, timestp, isCond) VALUES ("user2", "2024-02-08-12", 0);

I found that query outputs a row with count per instance and I just need to get the count of the number of row this outputs:

SELECT COUNT(DISTINCT isCond) AS counti FROM test 
WHERE timestp like "2024-02-08%"
GROUP BY user HAVING counti=2;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Found that this does the trick

    SELECT COUNT(*) FROM 
(SELECT COUNT(DISTINCT isCond) AS counti FROM test 
WHERE timestp like "2024-02-08%"
GROUP BY user HAVING counti>1) as myq;

  2. 0

    SELECT COUNT(*) AS count FROM tabl GROUP BY userId, isCondition, timestamp HAVING count> 1;

    Replace the tabl with your table

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

Back To Top
Search