Problem with Sum() function - 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

General SQL Server Forums

New to SQL Server Programming

Problem with Sum() function

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2011-07-14 : 08:04:17

The query
"Select sum(**) From Table" gives output of only total amount

I want the original data and in the bottom it should show the total instead of just total. Could you please tell me SQL query for that?

For e.g.
Item >> Sale
A >> 3 >
B >> 4
C >> 5
If I put here sql query Select Sum(Sale) FROM Table it just shows total 12. But I want whole data A >> 3.....and next to 5 it should put 12

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 08:17:38

You can either write two queries:


select Item, Sale from YourTable;
select sum(Sale) from YourTable;

Or, if you are on SQL 2005 or higher,


select Item, Sale, Sum(Sale) over() as Total From YourTable

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2011-07-14 : 09:22:00

You can also try ROLLUP:


SELECT Item, Sale FROM SalesTable
GROUP BY Item
WITH ROLLUP

or if you want the word total to be displayed:


SELECT (CASE GROUPING(Item) WHEN 0 THEN Item ELSE 'Total' END) AS ItemName, Sale FROM SalesTable
GROUP BY Item
WITH ROLLUP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 09:35:35

quote:
Originally posted by mohdowais

You can also try ROLLUP:

SELECT Item, Sale FROM SalesTable
GROUP BY Item
WITH ROLLUP

or if you want the word total to be displayed:

SELECT (CASE GROUPING(Item) WHEN 0 THEN Item ELSE 'Total' END) AS ItemName, Sale FROM SalesTable
GROUP BY Item
WITH ROLLUP

OS

In this case, clearly using ROLLUP is a better option than what I suggested, which was to rung the query twice.

I recall reading somewhere that ROLLUP is scheduled to be removed in a future version of SQL. I was searching for it - but could not find it. And, I can't remember what other construct would replace the same functionality if it indeed were to be deprecated. My mind must be playing games with me!!

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2011-07-14 : 09:41:06

Sunita, SQL Server used to have a COMPUTE BY clause that was very similar to ROLLUP, and has been deprecated in SQL 2005+

OS

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-14 : 10:00:43

quote:
Originally posted by mohdowais

Sunita, SQL Server used to have a COMPUTE BY clause that was very similar to ROLLUP, and has been deprecated in SQL 2005+

OS

Yes!!! Now I KNOW my mind was playing games with me!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources