Grouping question

Question

Post reply

Grouping question

sql_Reporter

SSCrazy

Points: 2329
More actions
April 28, 2010 at 5:29 am

#218274

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

Hi Buddy,how about you going through this following article and helping us help you?? 🙂

CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

When u do so, i am sure a lot of us will help u instantly...

So please post

1. Table Structures - CREATE TABLE scripts

2. Constraints or Indexes in the table, - CREATE SCRIPTS

3. SAMPLE DATA - INSERT INTO TABLE scripts

4. Desired output - some visual representation of this.

We could guess very little information from your post mate 🙁 !!

_ms65g_ SSCommitted Points: 1504 More actions · Answer 2

_ms65g_

SSCommitted

Points: 1504

April 28, 2010 at 6:17 am

#1158816

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

SELECT col0, SUM(col2) AS sum_col2, MAX(col3) AS max_col3



FROM your_table_name_here

GROUP BY col0

Lowell SSC Guru Points: 323482 More actions · Answer 3

ColdCoffee is so right; giving us the tools to help you is critical. with 180 posts, I'm sure you've seen the request more than once.

as a freebie, here's the CREATE TABLE and INSERT INTO you should have posted, as well as the formatted results from my solution i created....the code which produces the results i'll post later.



col0

-------- -------- ----------- -----------------------

A        A        30          2010-10-02 00:00:00.000

C        C        10          2010-10-02 00:00:00.000



CREATE TABLE #Example (

col0 varchar(8),

col1  varchar(8),

col2 int,

col3 datetime )

INSERT INTO #Example(col0,col1,col2,col3)

SELECT 'A', 'A','10','10/02/2010' UNION ALL

SELECT 'A','AB','20','09/02/2010' UNION ALL

SELECT 'C', 'C','10','10/02/2010'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323482 More actions · Answer 4

nearly the same as ms65g's reply:



SELECT

 col0,

 min(col1),

 sum(col2),

 max(col3)

 from #Example

 Group By col0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!