SELECT DISTINCT ON Simple Table Unique Values

Question

I have two tables, one with unique LTV (lifetime values) with around 3300 records and then the transaction log with more than 5000 transactions.

Whenever I run the following query it keeps showing me duplicate values. I just want to look up the person's first name and last name from the first column.

SELECT
SociAll.firstname,
SociAll.lastname,
SociLTV.Email,
SociLTV.LTV
FROM
SociAll
INNER JOIN SociLTV ON SociAll.Email = SociLTV.Email

Sometimes the same email address is repeated 3 or 4 times depending on the number of transactions from that given user, even though the LTV is the exact same value.

How can I have only 1 record per email address on this Query?

Answer 1

Try this:

SELECT
SociAll.firstname,
SociAll.lastname,
SociLTV.Email,
Sum(SociLTV.LTV)
FROM
SociAll
INNER JOIN SociLTV ON SociAll.Email = SociLTV.Email
GROUP BY SociAll.firstName,SociAll.LastName,SociAll.Email

You can also use COUNT() or MIN(), or MAX() etc. on the last column. If you don't care at all about the last column, remove it.

You can also do the following if you don't care at all about the SocilTV records

 SELECT DISTINCT
    SociAll.firstname,
    SociAll.lastname,
    SociLTV.Email,
    FROM
    SociAll
    INNER JOIN SociLTV ON SociAll.Email = SociLTV.Email