How do I return first value on joined tables with aggregated columns

I have a set of tables that I would like to build a view from, for example:

Member

ID ¦ Name
---¦------
1  ¦ John

Transaction

ID¦ MemberID
--¦-----------
1 ¦    1

TransactionPart

ID ¦ TransactionID ¦ SchemeID ¦ Value
---¦---------------¦----------¦-------
1  ¦       1       ¦    1     ¦  150
2  ¦       1       ¦    2     ¦  50

Scheme

ID ¦ EmailAddress
---¦--------------
1  ¦   x@x.com
2  ¦   y@y.com

A typical row in the view would contain the Name of the member, the total value of a transaction and ANY email address of one of the schemes that the transaction parts relate to.

For example:

Name ¦ TotalValue ¦ Email Address
-----¦------------¦---------------
John ¦    200     ¦    x@x.com

I came up with this statment below but it returns 2 separate rows if the email addresses differ.

SELECT 
   m.Name as Name, SUM(tp.Value) as TotalValue, s.EmailAddress as EmailAddress
FROM 
   Member m INNER JOIN开发者_如何学C 
   Transaction t ON m.ID = t.MemberID INNER JOIN 
   TransactionPart tp ON tp.TransactionID = t.ID INNER JOIN 
   Scheme s ON s.ID = tp.SchemeID
GROUP BY 
   m.Name, s.EmailAddress

Result:

Name ¦ TotalValue ¦ Email Address
-----¦------------¦---------------
John ¦    150     ¦    x@x.com
John ¦    50      ¦    y@y.com

How do I return a single row that contains either of the email addresses (possibly just the first found) with the aggregated value total? Is this possible given the examples shown?

Thanks in advance for any comments / answers!

You were almost there, all that's needed is to NOT group on the EmailAddres.

Use any of the aggregated functions to return a EmailAddress. (I have used MIN)

SQL Statement

SELECT   m.Name as Name
         , SUM(tp.Value) as TotalValue
         , MIN(s.EmailAddress) as EmailAddress
FROM     Member m 
         INNER JOIN Transaction t ON m.ID = t.MemberID 
         INNER JOIN TransactionPart tp ON tp.TransactionID = t.ID 
         INNER JOIN Scheme s ON s.ID = tp.SchemeID
GROUP BY 
         m.Name

Group only by m.Name & apply MAX (or MIN) on s.EmailAddress, will give you a single address ('max' with alphabetical ordering for strings):

SELECT 
   m.Name as Name,
   SUM(tp.Value) as TotalValue,
   MAX(s.EmailAddress) as EmailAddress
FROM 
   Member m INNER JOIN 
   Transaction t ON m.ID = t.MemberID INNER JOIN 
   TransactionPart tp ON tp.TransactionID = t.ID INNER JOIN 
   Scheme s ON s.ID = tp.SchemeID
GROUP BY 
   m.Name

I don't have SQL 2008 in front of me, but can you do a MIN on a varchar field?

SELECT 
   m.Name as Name, SUM(tp.Value) as TotalValue, MIN(s.EmailAddress) as EmailAddress
FROM 
   Member m INNER JOIN 
   Transaction t ON m.ID = t.MemberID INNER JOIN 
   TransactionPart tp ON tp.TransactionID = t.ID INNER JOIN 
   Scheme s ON s.ID = tp.SchemeID
GROUP BY 
   m.Name

An alternative is to perform some logic on the SchemeID and then use a correlated sub-query to get the email address...

SELECT 
   m.Name as Name,
   SUM(tp.Value) as TotalValue,
   (SELECT EmailAddress FROM Scheme WHERE ID = MIN(tp.SchemeID)) as EmailAddress
FROM 
   Member m INNER JOIN 
   Transaction t ON m.ID = t.MemberID INNER JOIN 
   TransactionPart tp ON tp.TransactionID = t.ID
GROUP BY 
   m.Name