total of sum
-
hi
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
-
You can use:
GROUP by ID WITH ROLLUP
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking
-
Hi,
See this thread.
http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause/
Thanks
Shatrughna
Shatrughna
-
i want to do something like this
(sum(isnull(PRIN_PMT))/ sum(sum(prin)) * (datediff(day,'09/30/2010',vc.acct_prd)/365.00) as prin_amount
that i need to place from view into temporary table and there are lots of sum function that is going to add into temporary table ,i dont know how to do rollup .
-
to help us help you...please post table creation / insert data /query scripts that will provide a representative sample of the data...based on the sample dat you provide, please provide required results.
we can then get to work 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
hi
here is my script,the font in bold this is what i want to do
SELECT
v.id,
v.salary
v.AC,
v.Year, vc.acct_prd,
SUM(ISNULL(v.PRIN_PMT,0)) AS CURR_PRIN_PMT,
SUM(ISNULL(v.BOOK_VALUE,0 )) AS CURR_BOOK,
SUM(ISNULL(v.END_ACCR_IN,0)) AS CURR_END,
(sum(isnull(v.PRIN_PMT,0))/ sum(sum(isnull(v.PRIN_PMT,0)) * (datediff(day,'09/30/2010',vc.acct_prd)/365.00) as W
into #temp1
from vc1 v
left outer join vp1 vp
on v.RUN_ID = vp.RUN_ID
group by v.run_id ,v.STVSHIFTID, v.ACPortExcept, v.Year, v.acct_prd
ORDER BY vc.run_id,
thaks
-
Hello
you have asked a sensible question....you have been asked to provide suitable scripts that willl assist someone on here to hopefully provide a solution to your pronlem.
At the moment only you, and you alone, understand your data and what you are attempting to achieve.
Now...pretend for a minute that you are trying to answer this question...what have you got????... a "script" ...cut and paste the following into SSMS
SELECTv.id,
v.salary
v.AC,
v.Year, vc.acct_prd,
SUM(ISNULL(v.PRIN_PMT,0)) AS CURR_PRIN_PMT,
SUM(ISNULL(v.BOOK_VALUE,0 )) AS CURR_BOOK,
SUM(ISNULL(v.END_ACCR_IN,0)) AS CURR_END,
(sum(isnull(v.PRIN_PMT,0))/ sum(sum(isnull(v.PRIN_PMT,0)) * (datediff(day,'09/30/2010',vc.acct_prd)/365.00) as W
into #temp1
from vc1 v
left outer join vp1 vp
on v.RUN_ID = vp.RUN_ID
group by v.run_id ,v.STVSHIFTID, v.ACPortExcept, v.Year, v.acct_prd
ORDER BY vc.run_id,
Does it parse, does it help you answer the question?, perhaps it may frustrate you
...I dont mean to offend, only to help...
so sample table/data/results will help please
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
the things in bold font is the only my prob,i want to do sum(sum(prin)) its not possible in sql
i want alternative of that
-
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.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
HI
THE THING IS WHAT I WANT IS
ID AMOUNT
1 2000
1 3000
1 5000
2 4500
2 5000
SO NOW I WANT OT DO CALCULATION LIKE FOR 1 ITS 10,000(SUM FOR 1)/19500(TOTAL SUM) AS AMT
I CANNOT USE GROUP BY AMOUNT HERE.
hOW TO DO THAT?
-
Something like this?
DECLARE @Example TABLE
(
id INTEGER NOT NULL,
amount MONEY NOT NULL
)
INSERT @Example
(id, amount)
VALUES
(1, $2000),
(1, $3000),
(1, $5000),
(2, $4500),
(2, $5000)
SELECT
e.id,
SUM(e.amount) AS id_sum,
SUM(SUM(e.amount)) OVER () AS total,
SUM(e.amount) / SUM(SUM(e.amount)) OVER () AS proportion
FROM @Example AS e
GROUP BY
e.id
ORDER BY
e.id
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
YA LIKE THIS,BUT CAN WE USE SUM(SUM(FIELD)) ????
-
daveriya (9/17/2011)
YA LIKE THIS,BUT CAN WE USE SUM(SUM(FIELD)) ????Just so you know, all caps in your posts is frequently identified as yelling at someone. I recommend avoiding the use of all caps in your posts. 😉
{Edit} Bad example removed.
--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/18/2011)
Leveraging Paul's code from above, please note the replacement of ORDER BY with WITH ROLLUP in the following code...That produces the following (unordered) set:
...which doesn't seem to take us very far forward.
I have provided examples using ROLLUP and SUM OVER () - now it's time for 'daveriya' to give it a go, and respond with something meaningful and easily consumable. I did receive a private message, but it made no sense in the context of the discussion so far, so I deleted it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply