skip to Main Content

Table name: DocumentationBook

id name_page
1 rule1
2 rule2
1 rule3
2 rule3
3 rule2
3 rule3

I have tried to do this,

SELECT COUNT(id)
FROM DocumentationBook
where name_page = "rule2" OR name_page = "rule3"

but it doesnt look right since i would like to show rule2 and rule3 following each other, or if there’s a method to give order of pages seen i would appreciate it so much.

2

Answers


  1. I created a SQLFIDDLE using your table data;

    The following query shall return you the expected result.

    select count(distinct id) as count_of_people
    from DocumentationBook 
    where id in (
      select id 
      from DocumentationBook 
      where name_page = 'rule2'
    ) and id in (
      select id 
      from DocumentationBook 
      where name_page = 'rule3'
    );
    

    Output :

    count_of_people
    2
    

    without seen another page using id in between

    If you want to add this condition; you can add as

    and id not in (
      select id 
      from DocumentationBook 
      where name_page in ('rule4', 'rule5', 'rule6')
    );
    

    Here rule numbers you can decide as per your requirement.

    Login or Signup to reply.
  2. This is a way to do it using WITH clause

    with cte as (
      select id
      from DocumentationBook
      where name_page in ('rule2', 'rule3')
      group by id 
      having count(1) = 2
    ),
    cte2 as (
      select id
      from DocumentationBook
      where name_page not in ('rule2', 'rule3')
    )
    select count(1)
    from cte 
    where id not in (select id from cte2)
    

    First cte to get ids that have seen rule2 and rule3

    Second cte to get ids that have seen other rules except rule2 and rule3

    Then we get only ids from fist cte that are not on cte2.

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search