total of sum
-
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
-
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)
Intro to Tally Tables and Functions -
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 totalYes, there are several ways to do this. Here's one example:
DECLARE @Example TABLE
(
id INTEGER NOT NULL,
amount MONEY 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)
Intro to Tally Tables and Functions -
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 totalYes, there are several ways to do this. Here's one example:
DECLARE @Example TABLE
(
id INTEGER NOT NULL,
amount MONEY 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)
Intro to Tally Tables and Functions -
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://qa.sqlservercentral.com/Forums/FindPost1177369.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
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://qa.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)
Intro to Tally Tables and Functions -
ok. i created 2 temp table and join with the third one to get my total .thanks everybody for your help
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply