SQL Sum Columns

Question

I want to get the following output:

Main table:

Email       | Group | id
a@gmail.com | Y     | 1
a@gmail.com | Y     | 2 
b@gmail.com | N     | 3
c@gmail.com | N     | 4

Join Table:

Email       | Value
a@gmail.com | 10
b@gmail.com | 20
c@gmail.com | 30

Desired result (only take the a@gmail.com value once, despite appearing in the first table twice):

Group  | Email Count | Sum
Y      | 1           | 10
N      | 2           | 50

Here is the sqlfiddle I've been playing around with: http://sqlfiddle.com/#!9/c2a24d/8

Answer 1

You were close in your SQLFiddle. You just needed to join on a distinct select.

SELECT  
e.Unsub as Unsub,
count(e.email) as EmailCount,
sum(c.sum) as EmailSum
FROM CountTable c
JOIN (select distinct email, Unsub from EmailsTable) e on c.email = e.email
GROUP BY e.unsub

SQLFiddle

Answer 2

First remove the duplicates, and then do the calculations

SQL DEMO

SELECT filter.`Unsub`, COUNT(*),  SUM(`sum`)
FROM (
        SELECT DISTINCT `Unsub`, `email` 
        FROM EmailsTable ) as filter
JOIN  CountTable    
  ON filter.`email` = CountTable.`email` 
GROUP BY  filter.`Unsub`

OUTPUT

| Unsub | COUNT(*) | SUM(`sum`) |
|-------|----------|------------|
|     N |        2 |         50 |
|     Y |        1 |         10 |