Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    24

    Unanswered: comparing two Count values

    Hi,

    If have a database where people can create multiple records using the same email address. Each time they create a new record they have had the choice of signing up to the mailing list or not. I now have to work out who is signed up and who is not.

    My rule is that if there are equal or less records that haven't signed up, then I don't mail them (the corralory is true - if they have said yes more times, then they are on the list. This seems fair.

    Table1

    Email Maillistyes
    test@email.com 1
    TEST@email.com 1
    test@email.com 0
    email@wxyz.com 1
    email@wxyz.com 0


    test@email.com would be on the list, email@wxyz.com would not.


    SELECT email, count(*) as yescount
    FROM Table1
    WHERE MailListYes = 1
    GROUP BY email

    gives me all emails that said yes, with a count of how many times e.g.
    test@email.com 2
    email@wxyz.com 1

    I can then do:
    SELECT email, count(*) as yescount FROM Table1 where MailListYes = 0 group by email
    to give me all of the ones that said no.

    I then resort to Excel to figure the rest out. Is it possible to simply produce an email list where the number of counts who said yes is more than the number who said no?

    I want to say this:
    SELECT email
    FROM Table1
    HAVING
    (COUNT (MailListYes) = 1) >
    (COUNT (MailListYes) = 0)
    GROUP BY Email

    but it is the wrong syntax

    I have played around with creating VIEWS but it might not be the best solution. Possibly a TEMP table would work better as I need to generate this from time to time?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    DECLARE @peeps table (
       email      char(20)
     , mail_style bit
    )
    
    INSERT INTO @peeps (email, mail_style) VALUES ('test@email.com', 1)
    INSERT INTO @peeps (email, mail_style) VALUES ('TEST@email.com', 1)
    INSERT INTO @peeps (email, mail_style) VALUES ('test@email.com', 0)
    INSERT INTO @peeps (email, mail_style) VALUES ('email@wxyz.com', 1)
    INSERT INTO @peeps (email, mail_style) VALUES ('email@wxyz.com', 0)
    
    -- Your current query
    SELECT email
         , Count(*)
    FROM   @peeps
    GROUP
        BY email
    
    -- Calculate number of inclusions and exclusions per email address
    SELECT email
         , Sum(CASE WHEN mail_style = 1 THEN 1 ELSE 0 END) As number_included
         , Sum(CASE WHEN mail_style = 0 THEN 1 ELSE 0 END) As number_excluded
         , Count(*) As total
    FROM   @peeps
    GROUP
        BY email
    
    -- Now you have two options
    -- 1) HAVING
    -- 2) Subquery
    
    -- 1
    SELECT email
         , Sum(CASE WHEN mail_style = 1 THEN 1 ELSE 0 END) As number_included
         , Sum(CASE WHEN mail_style = 0 THEN 1 ELSE 0 END) As number_excluded
         , Count(*) As total
    FROM   @peeps
    GROUP
        BY email
    HAVING Sum(CASE WHEN mail_style = 0 THEN 1 ELSE 0 END) < Sum(CASE WHEN mail_style = 1 THEN 1 ELSE 0 END)
    
    -- 2
    SELECT email
    FROM   (
            SELECT email
                 , Sum(CASE WHEN mail_style = 1 THEN 1 ELSE 0 END) As number_included
                 , Sum(CASE WHEN mail_style = 0 THEN 1 ELSE 0 END) As number_excluded
                 , Count(*) As total
            FROM   @peeps
            GROUP
                BY email
           ) As a_subquery
    WHERE  number_excluded < number_included
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2002
    Posts
    24
    Thank you for this. I used the HAVING solution so I didn't get caught up in parentheses. I also shortened it for my needs to the bare minimum so I now have the following (which works great!) - Cheers!!


    Code:
    SELECT email
    FROM table1
    GROUP BY email
    HAVING 
    SUM(CASE WHEN maillistyes = 0 THEN 1 ELSE 0 END) < SUM(CASE WHEN maillistyes = 1 THEN 1 ELSE 0 END)
    ORDER BY email ASC


    Quote Originally Posted by gvee View Post
    Code:
    DECLARE @peeps table (
       email      char(20)
     , mail_style bit
    )
    
    INSERT INTO @peeps (email, mail_style) VALUES ('test@email.com', 1)
    INSERT INTO @peeps (email, mail_style) VALUES ('TEST@email.com', 1)
    INSERT INTO @peeps (email, mail_style) VALUES ('test@email.com', 0)
    INSERT INTO @peeps (email, mail_style) VALUES ('email@wxyz.com', 1)
    INSERT INTO @peeps (email, mail_style) VALUES ('email@wxyz.com', 0)
    
    -- Your current query
    SELECT email
         , Count(*)
    FROM   @peeps
    GROUP
        BY email
    
    -- Calculate number of inclusions and exclusions per email address
    SELECT email
         , Sum(CASE WHEN mail_style = 1 THEN 1 ELSE 0 END) As number_included
         , Sum(CASE WHEN mail_style = 0 THEN 1 ELSE 0 END) As number_excluded
         , Count(*) As total
    FROM   @peeps
    GROUP
        BY email
    
    -- Now you have two options
    -- 1) HAVING
    -- 2) Subquery
    
    -- 1
    SELECT email
         , Sum(CASE WHEN mail_style = 1 THEN 1 ELSE 0 END) As number_included
         , Sum(CASE WHEN mail_style = 0 THEN 1 ELSE 0 END) As number_excluded
         , Count(*) As total
    FROM   @peeps
    GROUP
        BY email
    HAVING Sum(CASE WHEN mail_style = 0 THEN 1 ELSE 0 END) < Sum(CASE WHEN mail_style = 1 THEN 1 ELSE 0 END)
    
    -- 2
    SELECT email
    FROM   (
            SELECT email
                 , Sum(CASE WHEN mail_style = 1 THEN 1 ELSE 0 END) As number_included
                 , Sum(CASE WHEN mail_style = 0 THEN 1 ELSE 0 END) As number_excluded
                 , Count(*) As total
            FROM   @peeps
            GROUP
                BY email
           ) As a_subquery
    WHERE  number_excluded < number_included

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    My rule is that if there are equal or less records that haven't signed up, then I don't mail them (the corralory is true - if they have said yes more times, then they are on the list. This seems fair.
    So if someone has "signed up" a number of times, lets say 10 times, and now wants to unsubscribe, he will have to unsubscribe at least 10 times before it will take effect. (I don't know if this can be an issue, you haven't given any clue about the nature of the records people can create.)

    Don't you have a timestamp column included in the records (perhaps you should if it isn't present yet)? Last decision wins. Seems more logical and easier to implement.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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