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;
2
Answers
Found that this does the trick
SELECT COUNT(*) AS
count
FROM tabl GROUP BY userId, isCondition, timestamp HAVINGcount
> 1;Replace the tabl with your table