Grand Total/ Sum Issues - 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 2000 Forums

Transact-SQL (2000)

Grand Total/ Sum Issues

Author

Topic

janaquinn78
Starting Member

1 Post

Posted - 2007-07-03 : 18:13:35

I am writing a stored procedure a need help. I need to return three fields, one with a part number, the second with a sum of all transaction costs for that part, and the third with a total sum of all transaction costs for all parts. The number in the GRANDTOTAL column is the same for every part.

This is what I am looking for:

PART NUM COST GRANDTOTAL
12345-5 4.00 18.00
12345-6 3.00 18.00
12345-7 5.00 18.00
12345-8 6.00 18.00

Thx!

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-07-03 : 18:39:31

please provide:

1. table and index DDL for all relevant tables
2. sample data in the form of INSERT statements
3. expected output based on sample data

elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-03 : 19:02:41

[code]SELECT a.[PART NUM], a.[COST], b.[GRANDTOTAL]
FROM
(
SELECT t.[PART NUM],
[COST] = SUM(t.[TRANSACTION COST])
FROM [YOUR TABLE] t
GROUP BY t.[PART NUM]
) a
CROSS JOIN
(
SELECT [GRANDTOTAL] = SUM([TRANSACTION COST])
FROM [YOUR TABLE]
) b[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources