Tally number of people in 2 groups? Then display?

  • I was wondering if someone that is better at java and php than me, could give me a hand.

    I am looking for a code snippet, to give me a members number. What I am looking to do, is list all members from 2 groups, IE, if they are in group 1, and also in group 2, then count them as 1. Then list the number of total users that are in both group 1 and 2, so that I can display it.

    I'm not trying to list the actual members, just how many members are in groups 1 and 2.

    Thanks in advance :)

  • SQL
    1. SELECT userID, username FROM wcfN_user WHERE userID IN (SELECT userID FROM wcfN_user_to_group WHERE groupID = FIRST_GROUP_ID) AND userID IN (SELECT userID FROM wcfN_user_to_group WHERE groupID = SECOND_GROUP_ID);

    (untested)


    this allows you to receive the datasets from the database.

    return null;


    Browser: Firefox Nightly (64bit)

    Betriebssystem: Windows 10

  • An alternate solution with just two instead of three queries is:

    SQL
    1. SELECT userID, username
    2. FROM wcfN_user
    3. WHERE userID IN
    4. (SELECT userID
    5. FROM wcfN_user_to_group
    6. WHERE groupID IN (FIRST_GROUP_ID, SECOND_GROUP_ID)
    7. GROUP BY userID
    8. HAVING COUNT(groupID)=2
    9. );


    If you really only need the userID and not the username you can even shorten it to:

    SQL
    1. SELECT userID
    2. FROM wcfN_user_to_group
    3. WHERE groupID IN (FIRST_GROUP_ID, SECOND_GROUP_ID)
    4. GROUP BY userID
    5. HAVING COUNT(groupID)=2;
  • cyberlocc  

    Or perhaps did we (jens1o and me) missunderstand your request? After re-reading your post I think this solution (not optimized) might be what you wanted:


    This will output something like:


    Code
    1. +----+----+----+
    2. | A | B | AB |
    3. +----+----+----+
    4. | 11 | 48 | 7 |
    5. +----+----+----+

    Which essentially means: 11 users are in FIRST_GROUP, 48 users are in SECOND_GROUP and 7 users are in FIRST_GROUP as well as in SECOND_GROUP.

    Which is allways the full count of users...

  • This one was what I was after :) thank you.

  • Not to sound dumb, but I been trying to play with this, and have no idea how to make it display like you did lol.

    Also thats a little more than what I needed, Basically, I want it to check for people that are in Groups 1 and 2, and then print how many people are in them both. I dont need to print how many people are in 1 or 2, just how many are in both, and then print the number.

  • That display is the output of the sql command console. That's also the reason for those fancy ascii table art. ;) I like to interact with my database simply by using the mysql command via a login ssh shell.


    If you really just want one number this might be what you want:

    SQL
    1. SELECT COUNT(userID) AS number
    2. FROM
    3. (
    4. SELECT
    5. userID, COUNT(groupID)
    6. FROM wcfN_user_to_group
    7. WHERE groupID IN (FIRST_GROUP_ID,SECOND_GROUP_ID)
    8. GROUP BY userID
    9. ) AS T1;


    Full (console) output:

  • That display is the output of the sql command console. That's also the reason for those fancy ascii table art. ;) I like to interact with my database simply by using the mysql command via a login ssh shell.


    If you really just want one number this might be what you want:

    SQL
    1. SELECT COUNT(userID) AS number
    2. FROM
    3. (
    4. SELECT
    5. userID, COUNT(groupID)
    6. FROM wcfN_user_to_group
    7. WHERE groupID IN (FIRST_GROUP_ID,SECOND_GROUP_ID)
    8. GROUP BY userID
    9. ) AS T1;


    Full (console) output:


    Okay, thats where we are getting our wires crossed lol.


    I am not trying to look up the number, I am trying to display the number, on my site for public view.


    So its a Gaming Community, and we play various games, so I want on each games page to have the numbers displayed, how many people for instance play World of Warcraft, and how many are PvPers, PvErs, Crafters, Ect, with WOW and PVP, being 2 examples of ranks.


    I am trying to make something like this,

  • Well, yes the query is the basic. You'll probably need it anyway. I'm not firm in php. Basicly you'll need to embed the query (SQL part) in an php script which will generate the nice html output.