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

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

Thanks Ratchev.

It' work boot of the method