September 19, 2011 at 7:35 pm
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
September 20, 2011 at 8:29 pm
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
Change is inevitable... Change for the better is not.
September 20, 2011 at 9:05 pm
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
(
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
Change is inevitable... Change for the better is not.
September 20, 2011 at 9:05 pm
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
(
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
Change is inevitable... Change for the better is not.
September 20, 2011 at 9:32 pm
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
September 20, 2011 at 9:53 pm
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
Change is inevitable... Change for the better is not.
September 21, 2011 at 6:58 pm
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