Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Is it possible to select rows where the occurences of an id is > some value OR the value of a column is > some value?

Given a table:

userid activity count
1 RoomC 4
2 RoomB 1
2 RoomB 1
2 RoomC 1
3 RoomC 1
3 RoomC 1
3 RoomC 1
3 RoomC 1
4 RoomC 1
4 RoomC 1

Im trying to select the rows where a userid shows up more then X number of times, lets say >2, OR the value of a column is >2. In the above table I’m hoping for count > 2

So in the above database, the result of the query would give me userid 1, 2, 3

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

I’ve gotten the following query to get the instances where the occurences of userid > 2, but can I also somehow include the times where the column value count > 2 is also true?

SELECT *
FROM marktable
WHERE userid IN (
  SELECT userid
  FROM marktable
  GROUP BY userid
  HAVING COUNT(*) > 2
);

>Solution :

Add another condition in the WHERE clause:

SELECT *
FROM marktable
WHERE count > 2
   OR userid IN (
     SELECT userid
     FROM marktable
     GROUP BY userid
     HAVING COUNT(*) > 2
   );

Or, if you want all the rows of a userid that has a row with count > 2:

SELECT *
FROM marktable
WHERE userid IN (
  SELECT userid
  FROM marktable
  GROUP BY userid
  HAVING COUNT(*) > 2 OR MAX(count) > 2
);

See the demo.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading