Is it possible to include a column total as the last column in each row? E.G table contains column A, B and C. Column C has a numeric value in each row. Ten rows in the table and the value for each row in Column C is 10. Total for Column C is 100. Can I show this value of 100 against each row in the table as Column D?
Thanks in advance
First alter the table to add column D
Then update the column D as below
update [table_name]
set D = (select SUM(C) from [table_name])
SELECT PKeyID, ColB, ColC, TotalD
FROM MyTable AS A
JOIN
(
SELECT PKeyID, SUM(ColC) AS TotalD
FROM MyTable
WHERE ... any conditions here ...
GROUP BY PKeyID
) AS T
ON T.PKeyID = A.PKeyID
ORDER BY ...
Hi
Please find ur solution
SELECT A
,B
,C
,sum(c) OVER()
FROM #SampleData
/*
drop table #SampleData
create table #SampleData
(
A int null,
B int null,
C int null
)
insert into #SampleData select 1,1,10
insert into #SampleData select 1,2,10
insert into #SampleData select 1,3,10
insert into #SampleData select 1,4,10
insert into #SampleData select 1,5,10
insert into #SampleData select 1,6,10
insert into #SampleData select 1,7,10
insert into #SampleData select 1,8,10
insert into #SampleData select 1,9,10
insert into #SampleData select 1,10,10
select * from #SampleData
*/
the select 1 is optional, you can just use it like
SUM ( c ) OVER ( )
And, in versions earlier than SQL 2012, the order by clause is not supported for the SUM windowing function..
Oh didn't know that. It does not make sense anyway to have ORDER BY in SUM ( ) OVER ( )
In SQL 2012 and later versions, the ORDER BY clause in the SUM windowing function lets you compute running totals. This is a relief (at least to me) because without this feature, computing running totals used to be royal pain or royally slow. Now (using hhrr's sample data) it is as easy as
select *, sum(c) over(order by b) as RunningTotal
from #SampleData
which gives
A B C RunningTotal
1 1 10 10
1 2 10 20
1 3 10 30
1 4 10 40
1 5 10 50
1 6 10 60
1 7 10 70
1 8 10 80
1 9 10 90
1 10 10 100
In fact, in SQL 2012 there is a lot more flexibility in specifying the window. In the above query it uses the default window which is from PRECEDING UNBOUNDED to CURRENT ROW.
thanks James, that make sense. . . i guess i better upgrade my SQL Server now 
To 2012? 
Or 2014? 
Or 2016? 
The version that is supported and certified by the vendor to NOT break their application. Which will be tested, validated, certified for the current version no sooner than the release of the next version + 6 months...so, 2014 won't be certified with this application until late 2016... 
You are absolutely right.
The thing is I am the "vendor" here Some of our apps need to run on SQL 2000. Hopefully our client will move on to 2012 soon. But for now, we can't make use of the newer functionality in 2012 yet. 
I'd hate that ... having to continue to support legacy that far back when you know that much better solutions exist.