total of sum

Question

total of sum

Viewing 7 posts - 31 through 36 (of 36 total)

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

daveriya SSCrazy Points: 2352 More actions · Answer 1

thank you everybody ,i got my solution ,

but just one prob ,coz of to do total i ignore --having SUM(NULLIF(BOOK,0 )) is not null,what is the alternative of using having i can adjust in select statement

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

daveriya (9/19/2011)
thank you everybody ,i got my solution ,
but just one prob ,coz of to do total i ignore --having SUM(NULLIF(BOOK,0 )) is not null,what is the alternative of using having i can adjust in select statement

Great.... now it's your turn. 😉 Please post the solution you settled on so we can see what you did and ended up with. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 3

SQL Kiwi (9/17/2011)
daveriya (9/16/2011)
i have one column in which i need to do sum for every id and then i need to do total of that sum.is ther any function in sql to do grand total
Yes, there are several ways to do this. Here's one example:
DECLARE @Example TABLE
(
idINTEGER NOT NULL,
amountMONEY NOT NULL
)
INSERT @Example
(id, amount)
VALUES
(1, $10),
(1, $16),
(1, $12),
(2, $20),
(2, $27),
(2, $13)
SELECT
CASE
WHEN GROUPING(e.id) = 1 THEN 'Total'
ELSE CONVERT(VARCHAR(12), e.id)
END AS id,
SUM(e.amount) AS sum_amount
FROM @Example AS e
GROUP BY
GROUPING SETS ((), (id))
See the following Microsoft link for more information and examples:
http://msdn.microsoft.com/en-us/library/bb522495.aspx
By the way, the SUM aggregate automatically ignores NULLs. You do not need the ISNULL there.

Hmmm... based on our previous conversation, why didn't you include and ORDER BY in this particular example?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Jeff Moden (9/20/2011)
SQL Kiwi (9/17/2011)
daveriya (9/16/2011)
i have one column in which i need to do sum for every id and then i need to do total of that sum.is ther any function in sql to do grand total
Yes, there are several ways to do this. Here's one example:
DECLARE @Example TABLE
(
idINTEGER NOT NULL,
amountMONEY NOT NULL
)
INSERT @Example
(id, amount)
VALUES
(1, $10),
(1, $16),
(1, $12),
(2, $20),
(2, $27),
(2, $13)
SELECT
CASE
WHEN GROUPING(e.id) = 1 THEN 'Total'
ELSE CONVERT(VARCHAR(12), e.id)
END AS id,
SUM(e.amount) AS sum_amount
FROM @Example AS e
GROUP BY
GROUPING SETS ((), (id))
See the following Microsoft link for more information and examples:
http://msdn.microsoft.com/en-us/library/bb522495.aspx
By the way, the SUM aggregate automatically ignores NULLs. You do not need the ISNULL there.

Hmmm... based on our previous conversation, why didn't you include an ORDER BY in this particular example?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150467 More actions · Answer 5

Jeff Moden (9/20/2011)
Hmmm... based on our previous conversation, why didn't you include an ORDER BY in this particular example?

Just forgot. I used one in my final example http://www.sqlservercentral.com/Forums/FindPost1177369.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

SQL Kiwi (9/20/2011)
Jeff Moden (9/20/2011)
Hmmm... based on our previous conversation, why didn't you include an ORDER BY in this particular example?
Just forgot. I used one in my final example http://www.sqlservercentral.com/Forums/FindPost1177369.aspx

Ah... understood. I saw the one in your final example and wondered why you did use one in the prior example.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

daveriya SSCrazy Points: 2352 More actions · Answer 7

ok. i created 2 temp table and join with the third one to get my total .thanks everybody for your help