Grouping Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Grouping Question

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2010-04-28 : 07:19:10

Hi i've a problem i like to get a bit of help with.

I've 3 columns in a table which i'd like to group.

Dataset is below

col0 col1 col2 col3
A A 10 10/02/2010
A AB 20 09/02/2010
C C 10 10/02/2010

I'd like to group the col0 and get the sum on col2 and use the max date for col3

so expected result for A would be
col0 col1 col2 col3
A A 30 10/02/2010

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-28 : 07:41:12

Do you need col1 in your resultset?
How would you decide which value of col1 has to be in the resultset?

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-28 : 08:49:33

So you dont want c as there is not more than one data for it?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-28 : 10:38:49

as per sample data i think it should be something like


SELECT t.col0,t.col1,t1.Col2,t.col3
FROM Table t
INNER JOIN (SELECT col0,MAX(col3) AS col3,SUM(col2) AS Col2
            FROM Table
            GROUP BY col0)t1
ON t1.col0=t.col0
AND t1.col3=t.col3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources