T-SQL Grouping Issue

Question

I have 2 tables.

Contacts

ContactID pk
EmailAddress
FirstName
LastName
Address

Orders

OrderID pk
ContactID fk

I want to get the number or orders for each email address in Contacts like below

select 
    Contacts.EmailAddress,
    count(distinct Orders.OrderID) AS NumOrders
from 
    Contacts inner join Orders on Contacts.ContactID = Orders.ContactID
group by 
    Contacts.EmailAddress

Problem is, I also want the first name, last name, address. But I can't group by those because each email address in Contacts could have a different first name, lastname or address associated with it.

ie:

myname@email.com, Fred, Jackson, 123 Main St
myname@email.com, Bob, Smith, 456 Spruce St.

How can I change my query so that I can get the first name, last name and address for the most recent entry made in Contacts for that email address?

Thanks in advance!

Possible duplicate of [SQL join: selecting the last records in a one-to-many relationship](http://stackoverflow.com/questions/2111384/sql-join-selecting-the-last-records-in-a-one-to-many-relationship) — Tab Alleman, Apr 27 '16 at 19:51
Can you provide your date column that we can use to find the most recent entry? — Arthur D, Apr 27 '16 at 19:52
Is it fair to say that "most recent entry" would have the highest contactID? you don't include schema so we can see datatypes, PK definitions, etc. — n8wrl, Apr 27 '16 at 19:52

score 0 · Answer 1 · answered Apr 27 '16 at 19:58

Try this:

select 
    Contacts.Name,
    Contacts.FirstName,
    Contacts.LastName
    Contacts.EmailAddress,
    count(distinct Orders.OrderID) AS NumOrders
from 
    (
        select max(ContactID) as ContactID,
        EmailAddress
        from Contacts 
        group by EmailAddress
    ) MinContactForEachEMailAddress
    inner join 
    Contacts 
    on MinContactForEachEMailAddress.ContactID = Contacts.ContactID
    inner join 
    Orders 
    on Contacts.ContactID = Orders.ContactID
group by 
    Contacts.EmailAddress

JamieD77 · Accepted Answer · 2016-04-27T20:13:55.940

My first thought would be to use windowed functions.

SELECT  EmailAddress,
        FirstName,
        Lastname,
        [Address],
        EmailOrderCount
FROM    (SELECT c.EmailAddress,
                c.FirstName,
                c.LastName,
                c.[Address],
                COUNT(o.OrderID) OVER (PARTITION BY c.EmailAddress) EmailOrderCount,
                ROW_NUMBER() OVER (PARTITION BY c.EmailAddress ORDER BY c.ContactID DESC) Rn
        FROM    Contacts c
                JOIN Orders o ON c.ContactID = o.ContactID
        ) t
WHERE Rn = 1

Demo

another way would be to use CROSS APPLY to append the top 1 contact record to the summary rows.

SELECT  c.EmailAddress,
        COUNT(o.OrderID) NumOrders,
        ca.FirstName,
        ca.LastName,
        ca.[Address]
FROM    Contacts c
        INNER JOIN Orders ON c.ContactId = o.ContactID
        CROSS APPLY (
            SELECT TOP 1 
                    FirstName,
                    Lastname,
                    [Address]
            FROM    Contacts c2
            WHERE   c2.EmailAddress = c.EmailAddress
            ORDER BY c2.ContactID DESC) ca
GROUP BY c.EmailAddress,
        ca.FirstName,
        ca.LastName,
        ca.[Address]

quest4truth · Answer 3 · 2016-04-27T21:47:31.490

An easy way to do this is to make your original query a subquery and select from it. I'm making a slight change, because it's a better practice to group by your primary key than your email address. (Is it a safe bet that each contact has just one email address, and that the basic intent is to group by person?) If so, try this:

 SELECT DISTINCT c.EmailAddress, c.FirstName, c.LastName, c.Address, sub.NumOrders
 FROM
 (
      select 
          Contacts.ContactID
          count(distinct Orders.OrderID) AS NumOrders
      from 
          Contacts inner join Orders on Contacts.ContactID = Orders.ContactID
      group by 
          Contacts.ContactID
 ) sub
 JOIN Contacts c
 ON sub.ContactID = c.ContactID

If you really need to group by email address instead, then change the above subquery to your original query and change c.EmailAddress to sub.EmailAddress. Of course you may order the SELECT fields however best suits you.

Edit follows:

The ContactID must be a sequence number and you can continually put the same person in the table. So if you add the DISTINCT keyword in the outer query I believe that will give you what you need.

I need to group by email address. Email address isn't unique in the Contact table....that is really the big issue. The same email address can exist for multiple contactId's. I did what you said above, but, I still get more than 1 record per email address returned. — Cartch, Apr 27 '16 at 20:17
So just to clarify, more than one contact can have the same email address? — quest4truth, Apr 27 '16 at 20:19

score 0 · Answer 4 · answered Apr 27 '16 at 20:28

Another way to get what you want is using a CTE and taking the "maximum" row by using ROW_NUMBER.

;WITH CTE AS (
    SELECT C.ContactId, C.Name, C.FirstName, C.LastName, C.EmailAddress, 
        ROW_NUMBER() OVER (PARTITION BY EmailAddress ORDER BY ContactId DESC) RowNo
    FROM Contact C 
)
SELECT CTE.*, COUNT(o.OrderID) OVER (PARTITION BY CTE.EmailAddress) Cnt
FROM CTE
    JOIN Orders O on CTE.ContactID = O.ContactID
-- select the "maximum" row
WHERE CTE.RowNo = 1

T-SQL Grouping Issue

Contacts

Orders

4 Answers4