sql 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 2005 Forums

Transact-SQL (2005)

sql question

Author

Topic

ntn104
Posting Yak Master

175 Posts

Posted - 2011-09-23 : 16:55:39

If one account that showed many rows associated with that account. i would like to group that account to show only one row, but total for all rows. For example:

ID REC_DATE QUANTITY VALUE RECT_##
1234 20091205 10 $20,000.00 2345678
1234 20100609 20 $23,000.00 2345678
1234 20110812 30 $30,000.00 9876543

I expected result as:
ID REC_DATE QUANTITY VALUE RECT_##
1234 20110812 60 $73,000.00 9876543

I used below sql, it does not total the whole account, I wonder if you could help me re-write statement to make it work...thanks,

SELECT ID, MAX(REC_DATE) AS DATE, RECT_#
, SUM(QUANTITY) AS QUANTITY
, SUM(VALUE) AS VALUE
FROM TABLE1
GROUP BY ID, RECT_#

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-23 : 17:42:56

Hello,

How about something like;

SELECT 
	t.ID,
	d.REC_DATE,
	d.QUANTITY,
	d.VALUE,
	t.RECT_##
FROM TABLE1 t
JOIN
(
	SELECT
		ID,
		SUM(QUANTITY) AS QUANTITY,
		SUM(VALUE) AS VALUE,
		MAX(REC_DATE) AS REC_DATE
	FROM TABLE1 t
	GROUP BY ID
) d ON d.ID = t.ID AND t.REC_DATE = d.REC_DATE

HTH.

ntn104
Posting Yak Master

175 Posts

Posted - 2011-09-23 : 19:27:02

But the D.Value and D.Quantity will return total for one,not sum of value and quantity of all recpt_##. Probably, we may not need to include max(rec_date) in the sub-query, so that may work....I will try...

Thanks much!

quote:

Originally posted by ehorn

Hello,

How about something like;

SELECT 
	t.ID,
	d.REC_DATE,
	d.QUANTITY,
	d.VALUE,
	t.RECT_##
FROM TABLE1 t
JOIN
(
	SELECT
		ID,
		SUM(QUANTITY) AS QUANTITY,
		SUM(VALUE) AS VALUE,
		MAX(REC_DATE) AS REC_DATE
	FROM TABLE1 t
	GROUP BY ID
) d ON d.ID = t.ID AND t.REC_DATE = d.REC_DATE

HTH.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-24 : 09:36:11

quote:
Originally posted by ntn104

If one account that showed many rows associated with that account. i would like to group that account to show only one row, but total for all rows. For example:

ID REC_DATE QUANTITY VALUE RECT_##
1234 20091205 10 $20,000.00 2345678
1234 20100609 20 $23,000.00 2345678
1234 20110812 30 $30,000.00 9876543

I expected result as:
ID REC_DATE QUANTITY VALUE RECT_##
1234 20110812 60 $73,000.00 9876543

I used below sql, it does not total the whole account, I wonder if you could help me re-write statement to make it work...thanks,

SELECT ID, MAX(REC_DATE) AS DATE, MAX(RECT_#) AS Rect
, SUM(QUANTITY) AS QUANTITY
, SUM(VALUE) AS VALUE
FROM TABLE1
GROUP BY ID~~, RECT_#~~

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

ntn104
Posting Yak Master

175 Posts

Posted - 2011-09-26 : 17:12:40

Webfred,

It worked well with your edit.

Thanks much!!!!

quote:
Originally posted by webfred

quote:
Originally posted by ntn104

If one account that showed many rows associated with that account. i would like to group that account to show only one row, but total for all rows. For example:

ID REC_DATE QUANTITY VALUE RECT_##
1234 20091205 10 $20,000.00 2345678
1234 20100609 20 $23,000.00 2345678
1234 20110812 30 $30,000.00 9876543

I expected result as:
ID REC_DATE QUANTITY VALUE RECT_##
1234 20110812 60 $73,000.00 9876543

I used below sql, it does not total the whole account, I wonder if you could help me re-write statement to make it work...thanks,

SELECT ID, MAX(REC_DATE) AS DATE, MAX(RECT_#) AS Rect
, SUM(QUANTITY) AS QUANTITY
, SUM(VALUE) AS VALUE
FROM TABLE1
GROUP BY ID~~, RECT_#~~

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-09-27 : 03:25:46

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources