PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] Counting Grouped Emails-VBForums
Results 1 to 12 of 12

Thread: [RESOLVED] Counting Grouped Emails

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Resolved [RESOLVED] Counting Grouped Emails

    I have a lot of data tables, but the relevant ones to this post are: users, email_queue, and email.

    Basically, the client wants me to tell them if the users are using the system are not based on how many emails in the queue were added by that particular employee. Where it gets a little tricky is that in the email data table there is a column called sequence_id and that column is not unique. The way that they want me to count how many emails the user has added is grouped on the sequence_id field.

    So for example, if I have the following data in the email_queue table:
    Code:
    +----------+----------+---------+
    | queue_id | email_id | user_id |
    +----------+----------+---------+
    |    1     |     1    |    1    |
    |    2     |     2    |    1    |
    |    3     |     7    |    1    |
    |    4     |     8    |    1    |
    |    5     |     5    |    1    |
    |    6     |     1    |    2    |
    +----------+----------+---------+
    And the following data in the email table:
    Code:
    +----------+-------------+
    | email_id | sequence_id |
    +----------+-------------+
    |    1     |      1      |
    |    2     |      1      |
    |    3     |      4      |
    |    4     |      4      |
    |    5     |      3      |
    +----------+-------------+
    Then how would I structure my SQL query to where it counts the user with the user_id of 1 as 5 because: queue_ids 1 & 2 share the same sequence_id, 3 & 4 share the same sequence_id, and then 5 is on its own?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,038

    Re: Counting Grouped Emails

    I don't understand what you just said. You say:
    how would I [...] [count] the user with the user_id of 1 as 5
    It seems like the answer to that is to just count the occurrences of that user_id in the email_queue table. I don't see what the sequence_id in the email table has to do with that. Are you sure that you didn't mean to ask how to count that user as 3 rather than 5? That would make more sense because that would require grouping by the sequence_id.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,038

    Re: Counting Grouped Emails

    If I'm right about what you actually want then I would think that something like this would do the trick:
    sql Code:
    1. SELECT user_id, COUNT(sequence_id) AS email_count
    2. FROM
    3. (
    4.     SELECT DISTINCT eq.user_id, e.sequence_id
    5.     FROM email_queue eq INNER JOIN email e
    6.     ON eq.email_id = e.email
    7. ) A
    8. GROUP BY user_id
    If I'm wrong about what you want then I have no idea what you want.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Re: Counting Grouped Emails

    I'm sorry, I gave you the wrong result for user_id of 1; it should be 3 not 5. It is because queue_id's 1 and 2 have email_id's 1 and 2 respectively and since email_id's 1 and 2 share the same sequence_id in the email table, then that would count as only 1. The same applies for queue_id's 3 and 4. Then queue_id 5 counts as just one.

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,038

    Re: Counting Grouped Emails

    Quote Originally Posted by dday9 View Post
    I'm sorry, I gave you the wrong result for user_id of 1; it should be 3 not 5.
    That's what I thought, so the solution I provided should do what you need, I think. Did you try it?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    Re: Counting Grouped Emails

    If it helps, I think you can simplify JM's suggestion slightly:-
    Code:
    SELECT eq.user_id, Count(Distinct e.sequence_id)
    FROM email_queue eq INNER JOIN email e
    	ON eq.email_id = e.email
    Group By eq.user_id
    Haven't tested this against your data so I might have missed something but I believe they're equivalent
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  7. #7

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Re: Counting Grouped Emails

    Quote Originally Posted by jmcilhinney View Post
    That's what I thought, so the solution I provided should do what you need, I think. Did you try it?
    Not yet, I wanted to clarify my position and I had a customer come in for an insurance review. I'm going to give it a go here in a little bit and report back.

  8. #8

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Re: Counting Grouped Emails

    Quote Originally Posted by jmcilhinney View Post
    That's what I thought, so the solution I provided should do what you need, I think. Did you try it?
    Not yet, I wanted to clarify my position and I had a customer come in for an insurance review. I'm going to give it a go here in a little bit and report back.

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,038

    Re: Counting Grouped Emails

    Quote Originally Posted by FunkyDexter View Post
    If it helps, I think you can simplify JM's suggestion slightly:-
    Code:
    SELECT eq.user_id, Count(Distinct e.sequence_id)
    FROM email_queue eq INNER JOIN email e
    	ON eq.email_id = e.email
    Group By eq.user_id
    Haven't tested this against your data so I might have missed something but I believe they're equivalent
    I was wondering whether there was a way to combine the distinct and the count but hadn't looked myself. I'll store that one away for future reference.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  10. #10

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Re: Counting Grouped Emails

    Yours and FD's returned the same result, but I preferred FD's because it was more concise. Right now it returned the correct data, but also as of right now I only have 1 user, the sample data I provided was because I don't have all of the data inserted as of yet, so I will need to add some more data to truly test it. But off the top of your head, using the sample data in my first post, do you know if it'd return:
    Code:
    +---------+-------+
    | user_id | count |
    +---------+-------+
    |    1    |   3   |
    |    2    |   1   |
    +---------+-------+

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,982

    Re: Counting Grouped Emails

    I was wondering whether there was a way to combine the distinct
    Just a small note of caution, I'm fairly sure Count (Distinct) isn't ANSII compliant while your syntax is. That said, I'm not aware of a major DBMS that doesn't support it.


    I think it'd return a count of 2 for user id 1. The distinct SequenceIDs would be 1 and 3. It finds sequence 1 twice (via email ids 1 and 2) but this will only add 1 to the count. EMail IDs 7 and 8 don't have a corresponding record in email so won't produce a sequence id to count. Email ID 5 returns sequence id 3. So your distinct sequence ids are 1 and 3 => 2 distinct values.

    Have I misunderstood the requirement?
    Last edited by FunkyDexter; Oct 2nd, 2018 at 02:06 AM.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  12. #12

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    9,479

    Re: Counting Grouped Emails

    No, you're correct. It was me again not realizing that I didn't provide an email_id for 7 and 8.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width