# Thread: comparing two Count values

1. Registered User
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. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
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)

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```

3. Registered User
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```

Originally Posted by gvee
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)

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. Registered User
Join Date
Nov 2004
Posts
1,428