How to show field that not being group

Hi all.

I have this data:

id GroupId DDate Value
1 1 7/31/2008 5
2 1 7/31/2008 6
3 1 7/31/2008 2
4 2 7/31/2008 1
5 2 7/31/2008 2
6 2 7/31/2008 1

From this raw dataI want to show the data like this"
GroupId DDate TotalValue
1 7/31/2008 13
2 7/31/2008 4

current Query that I Use is

Select D.GroupId,(Select Top 1 DDate From MyTable Where
GroupId=D.GroupId)As DDate, Sum(D.Value) As TotalValue From MyTable D
Group By D.GroupId

But I want to remove the subquery, so my query will run faster.

Could it be done?

Thanks in Advanced

re: How to show field that not being group

You do not need the subquery, just group by both the groupid and ddate:

SELECT groupid, ddate, SUM(value) AS total_value
FROM MyTable
GROUP BY groupid, ddate;

If the date column has different values, then you can use MIN or MAX, based
on what makes sense:

SELECT groupid, MAX(ddate) AS ddate, SUM(value) AS total_value
FROM MyTable
GROUP BY groupid;


Plamen Ratchev
http://www.SQLStudio.com

re: How to show field that not being group

Thanks Ratchev.

It' work boot of the method