How to show field that not being group?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lemune - 31 Jul 2008 03:53 GMT

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

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 31 Jul 2008 04:17 GMT

Since all of your sample data happened on the same date, and based on your
existing query, I assumed that the MAX() *might* be needed here.

SELECT [GroupID], MAX(DDate), SUM([Value])
FROM MyTable
GROUP BY [GroupID];

Otherwise,

SELECT [GroupID], DDate, SUM([Value])
FROM MyTable
GROUP BY [GroupID], DDate;

On 7/30/08 10:53 PM, in article
bbd42b51-807b-4154-9dfb-7eb7228b2dcf@y38g2000hsy.googlegroups.com, "Lemune"
<alfredosilitonga@gmail.com> wrote:

> Hi all.
>
[quoted text clipped - 24 lines]
>
> Thanks in Advanced

Reply to this Message

Lemune - 31 Jul 2008 04:22 GMT

Thanks Aaron

It's work fine, booth of method.

Reply to this Message

Plamen Ratchev - 31 Jul 2008 04:18 GMT

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

Reply to this Message